CODEWITHSUNDEEP
mysqlinsert-update
Daniel Szalay
Daniel Szalay

Reputation: 4101

Converting INSERT commands to UPDATE

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?

Upvotes: 14

Views: 14713

Answers (4)

Asaph
Asaph

Reputation: 162821

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.

Upvotes: 17

dev-null-dweller
dev-null-dweller

Reputation: 29482

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

Upvotes: 0

dty
dty

Reputation: 18998

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

Upvotes: 0

Femaref
Femaref

Reputation: 61467

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.

Upvotes: 0

Related Questions