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

Question

In mysql, I have a table with 20 rows (example). I want to write the sort order (it is in the array with picID) in the SORT column from 1 to x (x is the number of elements 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

...

until 20 ...

But it does 20 mysql table updates ...

Can this be done in a more efficient way?

Jerry

Answer 1

One way to handle this is to use 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 to use control flow:

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

Pay attention to ELSEthe end. If you do not have it, it will set everything else clean!

Answer 2

, picID. 20 . , -, , SQL.