SQL : update statement with dynamic column value assignment

Question

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

Answer 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.

Answer 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.