Simple way to automatically convert INSERT statements to UPDATEs?

Question

I want to use the output of mysqldump to update entries in a live database. I don't want to delete the entries first, simple update statements are fine. Is there a simple way to convert the output of mysqldump which contains INSERT statements to the corresponding UPDATE statements?

It seems such a basic feature, so I'm sure someone created a tool or came up with a method to do it quickly, so people don't have to reinvent the wheel all the time by everyone writing their own scripts for this.

Edit: I'm looking for a generic solution, not one where I have to enumerate the actual table columns by hand. It's a generic problem, so I think there should be a table independent solution for it.

Answer 1

It's a matter of adding ON DUPLICATE KEY UPDATE clause

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Answer 2

You could restore the mysqldump data to a new temporary database, then use the multi-table UPDATE syntax to do the update.

UPDATE mydb.mytable AS dest JOIN tempdb.mytable AS origin USING (prim_key)
SET dest.col1 = origin.col1,
    dest.col2 = origin.col2,
    ...

Then drop the temp database.

Answer 3

Mchl's answer is valid another easy fix would be change 'INSERT' to 'REPLACE'. Both require a simple search / replace operation (I'd use sed). But if this were being run regularly then it would be a good candidate for replication / using timestamps to create a loader file only containing the modified/new records.