mysql conditional insert on duplicate update - multiple records

Question

How can I use the ON DUPLICATE UPDATE with a multiple value INSERT?

INSERT INTO tbl_name 
  (key_id,field1,filed2) 
VALUES
  (1,2,3),
  (1,5,6),
  (1,8,9);

What is your intended outcome? I can only get MySQL to allow (1,5,6) ON DUPLICATE KEY UPDATE key_id=key_id+1, but on execution I only have one row in the table, values: 2, 2, 3. — OMG Ponies
– OMG Ponies, Commented Feb 8, 2010 at 20:16
well the SQL is an example but the expected outcome would be to run one query that would insert if new, update if duplicate for multiple values. so if key_id 1, 3, and 5 are already in the DB and I run INSERT for 2, 3, 4 and 6. 2,4 and 6 should insert and 3 should update since it was a duplicate — Phill Pafford
– Phill Pafford, Commented Feb 8, 2010 at 20:20

Peter Lang · Accepted Answer · 2010-02-08 20:34:28Z

11

I can not try it right now, but can't you use this syntax

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

from the manual?

Guess your syntax would then look like this:

INSERT INTO tbl_name
  (key_id,field1,filed2) 
VALUES
  (1,2,3),
  (1,5,6),
  (1,8,9)
ON DUPLICATE KEY
  UPDATE field1=VALUES(field1), field2=VALUES(field2);

edited Feb 8, 2010 at 20:34

answered Feb 8, 2010 at 20:18

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

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

1 Comment

Phill Pafford Over a year ago

I must have missed this, thnx

Collectives™ on Stack Overflow

mysql conditional insert on duplicate update - multiple records

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related