CODEWITHSUNDEEP
mysql
aries.wandari
aries.wandari

Reputation: 97

Update multiple row (same column) with multiple value

I have Array ( [406] => 1 [407] => 3 [408] => 2 [409] => 7 [410] => 1 )

run as mysql query

UPDATE counter SET total = 1 WHERE id = 406;
UPDATE counter SET total = 3 WHERE id = 407;
UPDATE counter SET total = 2 WHERE id = 408;
UPDATE counter SET total = 7 WHERE id = 409;
UPDATE counter SET total = 1 WHERE id = 410;

I can only optimized query above by grouping same total value as below:

UPDATE counter
    SET total = 1
    WHERE name IN (406, 410);

Is there any way to optimize it better, rather than execute (loop) the update query one by one.

Upvotes: 1

Views: 1641

Answers (2)

charlan alves
charlan alves

Reputation: 394

You need this: 

UPDATE counter SET total = CASE 
   WHEN id = 406 THEN 1
   WHEN id = 407 THEN 3
   WHEN id = 408 THEN 2
   WHEN id = 409 THEN 7
   WHEN id = 410 THEN 1
   END

Upvotes: 4

Vivek Singh
Vivek Singh

Reputation: 2447

you can use key value pair to update like below

UPDATE counter SET total = '".$value."' WHERE id = '".$key."';

Upvotes: 0

Related Questions