Update 20 rows in mysql with x=x+1 values?

Question

Hi there

In mysql I have a table with like 20 rows (example). I want to write sort order (it is in array that carried picID's) to the SORT column from 1 to x (x is the number of items in this example x=20).

My array starts with: [10,15,1...]

I can do:

UPDATE table SET sort=1 WHERE picID=10
UPDATE table SET sort=2 WHERE picID=15
UPDATE table SET sort=3 WHERE picID=1

...

till 20...

But that makes 20 updates to mysql table...

Is it possible to do it any more efficient way?

Jerry

Answer 1

One way to handle this is using temporary tables:

CREATE TABLE tmp_sort (id INT, sort_order INT);
INSERT INTO tmp_sort VALUES (10, 1), (15, 2), (1,3);

UPDATE table, tmp_sort 
   SET table.sort = tmp_sort.sort_order 
 WHERE tmp_sort.id = table.picID;

Another way using control flow:

UPDATE table 
   SET sort = CASE picID
  WHEN 10 THEN 1
  WHEN 15 THEN 2
  WHEN 1  THEN 3
  ELSE sort END

Note the ELSE at the end. If you don't have that in there it will set everything else to blank!

Answer 2

Not really since you must update each one line specifying the picID. You'll have to issue 20 update commands. This appears to be the most efficient way of handling this case if don't want to issue a big SQL command.