ORDER BY with UPDATE query

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.

1 answer

  • answered 2018-01-11 20:49 TLPNull

    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