I have a table schema like this:
CREATE TABLE statuses
ALTER TABLE `statuses` ADD PRIMARY KEY (`id`, `lang`) ;
ALTER TABLE `statuses` CHANGE `id` `id` INT NOT NULL AUTO_INCREMENT ;
This table holds some data which might be in different languages. Therefore I want every row which corresponds to the same data but in different languages to have the same
id but different
lang. This way the dual key is unique. I want to use the following query to insert and update values.
INSERT INTO statuses (id, lang, name)
ON DUPLICATE KEY UPDATE name = VALUES(name)
It works fine if I want to update the values, but it breaks when I want to actually insert something because the autoincrement feature puts different values in
id column for every newly created row.
Here you can check SQLfiddle: http://sqlfiddle.com/#!2/62fbfd/1
Is it possible to correct my table structure or my sql query to do the job, or should I use multiple queries first to insert first row to indicate new
id and after that insert the rest of the rows with this newly created
id? I prefer to use just one query so that's why I ask here.
If you change your schema to this:
CREATE TABLE statuses ( id int, lang int, name varchar(30) ); ALTER TABLE `statuses` ADD UNIQUE INDEX (`id`, `lang`) ; ALTER TABLE `statuses` CHANGE `id` `id` INT;
Will that do what you want? The inserts worked when I tried them in your sqlfiddle...
You could solve your id issue (if you need auto_increment) by having another table that contains just the auto_increment values you need. So you insert into that table, get and id and then use that id in your inserts. But you cannot use auto_increment and then use the same id on several rows. If that makes any sense...
I solved my problem this way (by using two queries, but one fairly simple and with the function
INSERT INTO statuses (id, lang_id, name) VALUES (null,(SELECT lang_id FROM languages LIMIT 1),''); INSERT INTO statuses (id, lang_id, name) VALUES (LAST_INSERT_ID(),1,'Third'),(LAST_INSERT_ID(),2,'Trzeci'),(LAST_INSERT_ID(),3,'Terzo') ON DUPLICATE KEY UPDATE name = VALUES(name);