ON DUPLICATE KEY question

[bokehman]

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.

[r937]

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

[Immerse]

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

[r937]

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

[bokehman]

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

[r937]

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

insert

REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL — that either inserts or updates — see Section 12.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.

da manual... i haz it