I have a update to fix positions values inside a table. The update worked fine in MYSQL 5.5, but after upgrade to MYSQL 5.7 it doesnt work anymore. Now, the "order by" inside the derived table is ignored. So, the rows are updateds based in primary key, not in order by.
May someone help me? Why the order by is ignored in MYSQL 5.7 and how to fixed it?
Thanks!!!
OLD POSITION VALUES
ImageId Position
389 0
390 6
391 4
392 1
NEW POSITION VALUES, AFTER FIXED (MYSQL 5.5)
ImageId Position
389 1
390 4
391 3
392 2
NEW POSITION VALUES, AFTER FIXED (MYSQL 5.7)
ImageId Position
389 1
390 2
391 3
392 4
UPDATE
UPDATE Image as t1
INNER JOIN (
SELECT t.*, @rownum := @rownum + 1 AS newPosition
FROM Image t, (SELECT @rownum := 0) r
ORDER BY t.Position
) as td
ON t1.ImageId = td.ImageId SET t1.Position= td.newPosition
Best Answer
A derived table in SQL is a set, and a set by definition has no row order. Your attempt to use an imperative calculation
(@rownum := @rownum + 1)
within a set was wrong to begin with, and while it may have worked for a while, as you now realized, there is no guarantee for that. Most database engines will (correctly) raise an error if you try to use a similar query.The need that you are describing was addressed with window ranking functions which are supported as of MySQL 8. If you can upgrade to that, your solution will be easy and elegant with
ROW_NUMBER () OVER(PARTITION BY ImageID ORDER BY Position)
.If you can't use version 8, your only guaranteed solution is to use a correlated subquery, something along the lines of:
Disclaimer - Since you didn't provide DDL and sample data, the solution below isn't tested
Note that for large tables, this solution may not perform well, but it is guaranteed to work regardless of version as it follows SQL set rules.
This is valid ANSI SQL syntax. MySQL specifically may require some adjustments.