SQL : update statement with dynamic column value assignment
Solution 1
UPDATE mytable, (
SELECT @loop := MAX(col1)
FROM
mytable
) o
SET col1 = (@loop := @loop + 1)
What you encountered here is called query stability
.
No query can see the changes made by itself, or the following query:
UPDATE mytable
SET col1 = col2 + 1
WHERE col1 > col2
would never end.
Solution 2
Here's the way I'd do it:
SELECT MAX(col2) FROM mytable INTO @max;
UPDATE mytable
SET col2 = @max:=@max+1
WHERE id IN (1,2,3,4,5)
ORDER BY id;
I tested this on MySQL 5.1.30 and it works.
I set the @max
variable first just because I find @Quassnoi's trick of doing it in a Cartesian product to be unnecessary and less readable.
Note that MySQL supports ORDER BY
in an UPDATE
statement (this is not standard SQL), and you should do this, to ensure the values are updated in the order you want. Otherwise MySQL guarantees no specific order.
Joel
My sites: Rewcawl.com - A search engine for your browsing history. SoundCloudWall - Data mining SoundCloud users and tracks. BookmarkerPro - Search your bookmarks and monitor any page WebAlertPro - Competitive Website Content Monitoring
Updated on June 08, 2022Comments
-
Joel almost 2 years
Imagine the following sql query:
UPDATE MYTABLE SET COL2 = (SELECT COL2 + 1 FROM (SELECT MAX(COL2) FROM MYTABLE) AS X) WHERE ID IN (1,2,3,4,5)
Assume that before the update is executed MAX(COL2) is 1.
My intention is that for the update where ID=1 COL2 is updated to 'max(COL2) + 1' (i.e. 2), and that for subsequent updates 'MAX(COL2) + 1' is re-evaluated, so that for ID=2, COL2=3 and ID=3, COL2=4 etc...
What actually happens is that for all rows (ID=1,2,3,4,5), the value of COL2 is 2.
Is there a smart way to have the value of MAX(COL2) +1 "re-evaluated" at each update? I realize there may be performance issues with doing this, but I am curious none-the-less! Is there a better alternative (that does not involve multiple update statements) ?
BTW: incase you are wondering about the syntax used for the above query (the nested inner table) see here: SQL : Using the target table in an UPDATE statement in a nested FROM clause