Converting INSERT commands to UPDATE

Question

Hey there,

I have two INSERT commands, that are useless to me like that because the two sets of rows - the ones that are already in the table, and the ones I have as INSERT commands - are not disjunct. Both commands insert lots of rows, and lots of values.

Therefore I get the duplicate entry error if I want to execute those lines.

Is there any easy way to 'convert' those commands into UPDATE?

I know this sounds stupid, because why do I make INSERT commands, if I want to UPDATE. Just to make it a clear scenario: another developer gave me the script:)

Thanks in advance, Daniel

EDIT - problem solved

First I created a table and filled it up with my INSERT commands, then I used the following REPLACE command:

REPLACE
    INTO table_1
SELECT *
    FROM table_2;

This can originally be found at: http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables

Answer 1

You would have to rewrite them to updates by hand. If I encouter such a problem, I query for the count of certain primary key first, if none is found I insert a generic dataset and update it afterwards. By this, new data can be added and already existing data will be updated, and you don't have to differentiate between inserting new data and updating data.

Answer 2

For MySQL, you can use either the INSERT IGNORE or the INSERT ... ON DUPLICATE UPDATE syntaxes. See the MySQL reference manual

Answer 3

MySQL's REPLACE keyword does this. Simply replace the INSERT keyword in your queries with the word REPLACE and it should update the rows instead of inserting new ones. Please note that it will only work if you're inserting a primary key or unique key column.

Answer 4

You can easily modify your queries to update duplicate rows, see INSERT ... ON DUPLICATE KEY syntax in MySQL