mysqlsqldatabasedatabase-backupsmysql-backup

How to insert record only if not exist based on primary key? (comparing with query itself instead of table)


A migration of a database has just been performed, and due to some accident of character encoding, some records are not input and I don't know which records are missing. I have a full SQL dump of the previous database, but the previous database is down.

I don't have the right to create a new table. As the system is in use already, it is impossible to clear the table and dump again. As the dump of the previous table looks like this:

INSERT INTO `surveys` (`id`, `column2`, `column3`, ...., `columnN`) VALUES
    (1, 'value2', 'value3', ...., 'valueN'), 
    (2, 'value2b', 'value3b', ..., 'valueNb'),
    .................
    (x, 'value2x', 'value3x', ..., 'valueNx')

If one row has primary key (id in this case) duplicated, the whole chunk is not updated. Is there a way to add a checking so that only the missing rows are added into the database?


Solution

  • Have you tried INSERT ... ON DUPLICATE KEY UPDATE ?

    INSERT INTO `surveys` (`id`, `column2`, `column3`, ...., `columnN`) VALUES
        (1, 'value2', 'value3', ...., 'valueN'), 
        (2, 'value2b', 'value3b', ..., 'valueNb'),
        .................
        (x, 'value2x', 'value3x', ..., 'valueNx')
    ON DUPLICATE KEY UPDATE `id`=`id`