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 
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?
insert
da manual… i haz it
