Insert multiple values with duplicate key and different updates

Question

I'm able to update my table when mykey (primary key) exists, or insert when mykey does not exist, with this query:

INSERT INTO customers (id, customer_id, page_id, mykey, hits) 
 VALUES 
(NULL, 1, 1, 23, 49) ON DUPLICATE KEY UPDATE hits=hits+49;

Works fine. Now I can't figure out how to add multiple values in one query. What I want is this:

INSERT INTO customers (id, customer_id, page_id, mykey, hits) 
 VALUES 
(NULL, 1, 1, 23, 49) ON DUPLICATE KEY UPDATE hits=hits+49,
(NULL, 2, 2, 56, 11) ON DUPLICATE KEY UPDATE hits=hits+11,
(NULL, 3, 3, 81, 14) ON DUPLICATE KEY UPDATE hits=hits+14;

But that doesn't work. Is it possible to insert or update multiple values like this in one query?

user1032531 · Accepted Answer · 2015-04-20 23:55:58Z

1

INSERT INTO customers (id, customer_id, page_id, mykey, hits) 
 VALUES 
(NULL, 1, 1, 23, 49),
(NULL, 2, 2, 56, 11),
(NULL, 3, 3, 81, 14)
ON DUPLICATE KEY UPDATE hits=hits+VALUES(hits);

edited Apr 20, 2015 at 23:55

answered Apr 20, 2015 at 23:52

user1032531

26.5k77 gold badges248 silver badges422 bronze badges

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

1 Comment

John McMahon Over a year ago

Minor syntax error, missing commas after the first two sets of values.

John McMahon · Accepted Answer · 2015-04-21 00:08:09Z

0

Do the ON DUPLICATE KEY UPDATE once at the end:

INSERT INTO customers (id, customer_id, page_id, mykey, hits) 
 VALUES 
(NULL, 1, 1, 23, 49),
(NULL, 2, 2, 56, 11),
(NULL, 3, 3, 81, 14) 
ON DUPLICATE KEY UPDATE hits=hits+VALUES(hits);

edited Apr 21, 2015 at 0:08

answered Apr 20, 2015 at 23:51

John McMahon

1,6151 gold badge16 silver badges21 bronze badges

2 Comments

Aiken Over a year ago

Doens't work, because all update rows would have hits=hits+14.

John McMahon Over a year ago

@Aiken Thanks, missed that!

Collectives™ on Stack Overflow

Insert multiple values with duplicate key and different updates

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related