1

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.

aries.wandari
  • 97
  • 1
  • 10
  • There is no clearer way. You can try using case if you have small number of elements http://stackoverflow.com/questions/25674737/mysql-update-multiple-rows-with-different-values-in-one-query You still have to iterate to create the query in any particular language. – bitkot Mar 26 '15 at 08:07

2 Answers2

4

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
Damian Kozlak
  • 7,065
  • 10
  • 45
  • 51
charlan alves
  • 384
  • 3
  • 17
0

you can use key value pair to update like below

UPDATE counter SET total = '".$value."' WHERE id = '".$key."';
Vivek Singh
  • 2,453
  • 1
  • 14
  • 27
  • thanks for your answer. But my question is how to optimize the query without need to execute (loop) the update one by one. – aries.wandari Mar 26 '15 at 07:44