14

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: How can I merge two MySQL tables?

Community
  • 1
  • 1
Daniel Szalay
  • 4,041
  • 12
  • 57
  • 103

4 Answers4

17

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.

Asaph
  • 159,146
  • 25
  • 197
  • 199
  • 1
    Thank you! I will make an edit to explain detailedly my solution. – Daniel Szalay Sep 11 '10 at 21:44
  • 3
    This can cause problems. Using replace will delete the row with the duplicate key and write the entire new row. This can mess up your indexes and add overhead. It is more proper to use 'on duplicate...update' which will retain your indexes. Replace is better used on one time scenarios where you indend to reindex, rather than repeated queries to update a table. – Aaron Martin Aug 19 '17 at 23:11
  • 1
    Sorry, had to downvote as this indeed will mess up your data! Especially when other tables rely on your primary ids – Sliq Sep 13 '19 at 14:32
  • Yes, use with caution and do proper testing. But using this saved me a lot of hours of manually converting INSERT statements into UPDATE statements. – James John McGuire 'Jahmic' Oct 05 '20 at 17:31
0

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.

Femaref
  • 60,705
  • 7
  • 138
  • 176
0

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

dty
  • 18,795
  • 6
  • 56
  • 82
0

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

dev-null-dweller
  • 29,274
  • 3
  • 65
  • 85