11

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.

11
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)

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

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);
  • 3
    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 Jan 19 '18 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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