Converting INSERT commands to UPDATE

mysql insert-update
Daniel Szalay picture Daniel Szalay · Sep 11, 2010 · Viewed 11.3k times · Source

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?

Answer

Asaph picture Asaph · Sep 11, 2010

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.

Related questions

Insert into a MySQL table or update if exists

I want to add a row to a database table, but if a row exists with the same unique key I want to update the row. For example: INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19); Let’s say …

Read More
Update a column in MySQL

I have a table table1 with three columns and a bunch of rows: [key_col|col_a|col_b] I want to update col_a with a set of values (i.e. leaving col_b unchanged), something like this: INSERT …

Read More
INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE

I'm doing an insert query where most of many columns would need to be updated to the new values if a unique key already existed. It goes something like this: INSERT INTO lee(exp_id, created_by, location, animal, starttime, …

Read More