15

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand.

For instance, two updates into 1 query:

UPDATE mytable SET fruit='orange', drink='water', food='pizza' WHERE id=1;

UPDATE mytable SET fruit='strawberry', drink='wine', food='fish' WHERE id=2;

I don't understand what the CASE WHEN .. THEN ... END works and how to use it.

Wonder if someone could help me on this.

1

2 Answers 2

15
UPDATE mytable SET
    fruit = CASE WHEN id=1 THEN 'orange' ELSE 'strawberry' END,
    drink = CASE WHEN id=1 THEN 'water'  ELSE 'wine'       END,
    food  = CASE WHEN id=1 THEN 'pizza'  ELSE 'fish'       END
WHERE id IN (1,2);

Personally, using CASE WHEN THEN END looks clumsy.

You could code this using the IF function.

UPDATE mytable SET
    fruit = IF(id=1,'orange','strawberry'),
    drink = IF(id=1,'water','wine'),
    food  = IF(id=1,'pizza','fish')
WHERE id IN (1,2);

Give it a Try !!!

CAVEAT : CASE WHEN THEN END is only handy when dealing with multiple values (more than 2)

2
  • Nice, I didn't knew about this IF function. Can you just explain: = IF(id=1, Why do you need this ? Commented Jun 29, 2014 at 4:50
  • 7
    can i use this for updating around 100k records in sinle query ?
    – user16108
    Commented Oct 30, 2015 at 1:19
7

INSERT ... ON DUPLICATE KEY UPDATE

You will need to write very complicated conditions if you want to update more than two rows. In such a case you can use INSERT ... ON DUPLICATE KEY UPDATE approach.

INSERT into `mytable` (id, fruit, drink, food)
VALUES
    (1, 'orange', 'water', 'pizza'),
    (2, 'strawberry', 'wine', 'fish'),
    (3, 'peach', 'jiuce', 'cake')
ON DUPLICATE KEY UPDATE
    fruit = VALUES(fruit), 
    drink = VALUES(drink), 
    food = VALUES(food);
4
  • 5
    Note that this increases the autoincrement value for the table, if you're using autoincrement. For high throughput tables that might be undesirable. More info stackoverflow.com/a/23517191/2560641
    – Juliano
    Commented Jan 19, 2018 at 12:39
  • 1
    One issue with ON CONFLICT is that it doesn't work if you want to omit a NOT NULL column that doesn't need updating: stackoverflow.com/questions/48816629/… Commented Jan 13, 2023 at 5:19
  • 1
    @CiroSantilliOurBigBook.com AFAIK there are no ON CONFLICT feature in MySQL
    – Yaroslav
    Commented Jan 21, 2023 at 2:39
  • My brain JOINed both. Does ON DUPLICATE KEY UPDATE work with NULL constraint BTW on MySQL? Commented Jan 21, 2023 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.