Search code examples
mysqlinsertinsert-select

Inserting unique rows without updating old ones

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.

Solution

  • 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)