ORDER BY with UPDATE query

Question

I'd like to update the field1 in the below query with a certain order. As in start updating from the smaller number to the biggest. (simplified names)

UPDATE table t1, (SELECT @temp := 0) a, (SELECT @temp_2 := 0) b 
SET field1 = (CASE
    WHEN id = X THEN (@temp := @temp+1)
    WHEN id = Y THEN (@temp_2 := @temp_2+1)
END)
WHERE ( id = X OR id = Y )
ORDER BY field1 ASC;

I tried using ORDER BY but I got and error Incorrect usage of UPDATE and ORDER BY.

I understand these , (SELECT @temp := 0) a, (SELECT @temp_2 := 0) b create the issue, but I'd like to avoid using a separate query to set them.

Take a look at https://stackoverflow.com/a/9080506/3179169 for usage or UPDATE and ORDER BY. — clinomaniac, Jan 11 '18 at 20:46
Why don't you want to use a separate query? That will solve the problem easily. — Barmar, Jan 11 '18 at 21:26
Thats what I ended up doing, I was just being silly and wanted to avoid it, since that specific query will run quite frequently — John James, Jan 11 '18 at 21:30

score 0 · Answer 1 · answered Jan 11 '18 at 20:49

0

All SQL updates are atomic, so you will update everything or nothing, and the order have no difference, but I think I understood what you trying to do, for this u will need multiples updates, or run over a cursor

answered Jan 11 '18 at 20:49

TLPNull

475
4
12

I understand that I will update everything, but the order does have a difference here. I'd like the field1's data to get the lowest numbers from the variables starting from the smallest. Also cursors are generally slow and in my case that is an overkill – John James Jan 11 '18 at 20:52
Yes, thats what I understood then, you can use cursor for this, and run over your table one by one. and put the order on the cursor declaration – TLPNull Jan 11 '18 at 20:53

ORDER BY with UPDATE query

1 Answers1