iPhone Developer iPhone Developer - 3 months ago 8
MySQL Question

Duplicate key update with multiple inserts

How can I properly manage on duplicate key update for the following SQL query where I've multiple inserts going on?

INSERT into user(id, first, last)
VALUES(1, 'f1', 'l1'), (2, 'f2', 'l2')
ON DUPLICATE KEY UPDATE first = 'f1', last = 'l1'; // what about f2/l2?


Question:
how can I specify multiple key update values for the above query or help with a lateral thinking please.

Overview: the project is for synchronizing purposes from a remote json feed.

Answer

Use VALUES:

INSERT into user(id, first, last) 
    VALUES(1, 'f1', 'l1'), (2, 'f2', 'l2') 
    ON DUPLICATE KEY UPDATE
        first = VALUES(first),
        last = VALUES(last); 

This is like a function (really syntactic sugar) that says to get the value passed in for the insert to the row.