Inserting unique rows without updating old ones

Question

I have two similar tables in different databases, I want to insert into one from the other one. At the same time I want to ensure that every time mySql encounters a 'duplicate id' error it inserts the row at the bottom of the new table and assigns a new unique id. I have tried:
INSERT...SELECT...ON DUPLICATE KEY UPDATE
But I can't find a way to get it to insert into a new row if it finds 'duplicate keys' instead of updating the previous row.

Is the 'key' an autoincrement value? If so, I may have a solution for you. Try 'INSERT INTO `table2` SELECT NULL, col1, col2, col3 FROM table1' I don't have the exact syntax at hand, but it should work that way. — ATaylor, Jul 23 '12 at 13:36
yes the 'key' is an autoincrement field. Thanks, I'll try this. — IROEGBU, Jul 23 '12 at 13:40
You're welcome. It'd be nice to hear if you get it to work :) — ATaylor, Jul 23 '12 at 13:42
@ATaylor I tried your code... it worked :D I think you should take a look at what eggyal came up with #cleanCode. Thanks! — IROEGBU, Jul 23 '12 at 14:39
That's good to hear. I also posted it as regular answer, but of course eggyval's answer is far superior. You may want to give him 'correct answer' status. ;) — ATaylor, Jul 23 '12 at 14:45

eggyal · Accepted Answer · 2012-07-23T13:50:00.123

2

If you can assign new id to every record being copied to the destination table, irrespective of whether the id in the source table existed previously in the destination or not, you can simply not provide the id and allow MySQL to resort to the default NULL value that causes an auto_increment value to be assigned:

INSERT INTO destdb.tbl (colA, colB, colC)
SELECT colA, colB, colC
FROM   srcdb.tbl

Otherwise, you will need to use something like NULLIF() to set id explicitly to NULL where it already exists, based on joining the tables together in the SELECT:

INSERT INTO destdb.tbl (id, colA, colB, colC)
SELECT NULLIF(src.id, dst.id), src.colA, src.colB, src.colC
FROM   srcdb.tbl src LEFT JOIN destdb.tbl dst USING (id)

edited Jul 23 '12 at 13:50

answered Jul 23 '12 at 13:38

eggyal

122,705
18
212
237

Hmm, I didn't know about NULLIF. I shall try to remember it, if I should ever need something like this. +1 – ATaylor Jul 23 '12 at 14:44
@ATaylor: There are quite a few handy functions buried away in MySQL; it's well worth spending an hour scanning through the manual pages when bored on a train. Has saved me so much pain! :) – eggyal Jul 23 '12 at 15:05
Yeah, I know exactly what you mean :) Unfortunately 'train rides' with internet access are so very rare for me :D – ATaylor Jul 23 '12 at 15:09

score 1 · Answer 2 · answered Jul 23 '12 at 13:45

According to the documentation at http://dev.mysql.com/doc/refman/5.1/de/insert-select.html, this may be achieved with the query:

INSERT INTO `table2` SELECT (NULL, col1, col2, col3) FROM `table1`

This way, the autoincrement-value is left with 'NULL', causing the engine to give it a new AI-value instead of trying to force the existing one in there.

Please don't stone me for the syntax, I haven't tested this.

score 0 · Answer 3 · answered Jul 23 '12 at 13:46

If the id is autoincrement:
Create an additional column in the second table with a reference to the id value in the first table
Sample Select Query: 

SELECT unique_id, data FROM table_1 WHERE id='$id'

Sample Insert Query: 

INSERT INTO table_2 (table_1_unique_id, data) VALUES ($unique_id_from_first_table, $data)

This will solve any duplicate id problems and allow referencing between the two tables

Inserting unique rows without updating old ones

3 Answers3