My table has col1 and col2 as a joint PRIMARY KEY.

INSERT INTO mytable ( col1, col2)
VALUES ('val1', 'val2'), ('val3', 'val4'), ('val5', 'val6')

How do I update col2 to the new values if the insert fails. If I use ON DUPLICATE KEY UPDATE I can only set them all to the same value, not individual values.

i believe ON DUPLCATE KEY UPDATE is not appropriate here

you probably want to use INSERT IGNORE so that if you do attempt to add a {col1,col2} pair that already exists, the insert statement continues without failing

Isn’t this a case for REPLACE INTO? Or am I misunderstanding the question?

i think you understood fine :slight_smile:

this appears to be a relationship table (two-column PK, each probably an FK)

REPLACE INTO will delete the row first, before inserting the new one

INSERT IGNORE doesn’t do either, and so is more efficient here

but this is due entirely to the fact that there are no data (non-key) columns in the table

add a datetime_added column, though, and then it’s between REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE, and i prefer the latter because a single update is more efficient than a delete and insert


What does REPLACE INTO do if the row doesn’t exist?


da manual… i haz it