1

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.

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks Gordon, can you please point me to multiple update statement (similar to my insert)
can you please also add documentation link for VALUES function and other similar functions
dev.mysql.com/doc/refman/5.7/en/…
just a clarification, VALUES(first) will use existing value from the table column, what if I want to use value from my query, how to update with my given value in the query, context is synchronization, where user exists in both databases but maybe last name got changed, so I wanna update l1 and l2 in my table, or maybe it's doing that please clear my confusion.
@iPhoneDeveloper . . . VALUES(first) uses the value provided in the insert statement, not the value in the table. This is a no-op if the values are the same; otherwise, it updates with the new values.