insert on duplicate key update with multiple values

Question

When using insert... on duplicate key update, what is the syntax to update multiple columns and not update some columns?

i wrote the following query:

INSERT INTO table (id,number,text) VALUES (1,2,3),(3,5,"i want this remain the previous value and not change"),(1,2,3)
  ON DUPLICATE KEY UPDATE id=VALUES(id), number=VALUES(number), text=VALUES(text);

how to not change some values and let them remain it's previous value?

Alma Do · Accepted Answer · 2014-01-16 07:31:35Z

1

Do it with IF:

INSERT INTO table (id,number,text) VALUES (1,2,3),(3,5,"ZZZ"),(1,2,3)
  ON DUPLICATE KEY UPDATE id=VALUES(id), number=VALUES(number), text=IF(VALUES(text)="ZZZ", text, VALUES(text));

-for multiple values use IN instead of =:

INSERT INTO table (id,number,text) VALUES (1,2,3),(3,5,"ZZZ"),(1,2,"YYY")
      ON DUPLICATE KEY UPDATE id=VALUES(id), number=VALUES(number), text=IF(VALUES(text) IN ("ZZZ", "YYY"), text, VALUES(text));

edited Jan 16, 2014 at 7:31

answered Jan 16, 2014 at 7:01

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

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

1 Comment

Mohammad Masoudian Over a year ago

can you say me how to use IN instead of =

Collectives™ on Stack Overflow

insert on duplicate key update with multiple values

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