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: http://stackoverflow.com/questions/556237/sql-using-the-target-table-in-an-update-statement-in-a-nested-from-clause

Answer 1

It should be re-evaluating it at each insert; presumably the max of COL2 is not changing. Are you sure you don't want to select MAX(COL1)? Reading your question again, you mixed up the terms insert and update (which is now fixed), and I got thrown for a loop.

---

You're trying to update many records at once but have it act as if it's updating each individually. This will not work as you're intending.

You could create a stored procedure to loop through each record WHERE ID IN (1,2,3,4,5) and update individually. Or, since you're only doing 5 rows, why not just copy and paste the statement five times and change the WHERE clause to WHERE ID = 1 (and 2, 3, 4, 5 on subsequent lines).

Answer 2

declare @loop int
select @loop = 1

UPDATE MYTABLE
SET COL1 = @loop,
    @loop = @loop+1
WHERE ID IN (1,2,3,4,5)

Answer 3

Is there a smart way to have the value of MAX(COL1) +1 "re-evaluated" at each insert?

Not unless you update the rows one-by-one :(

This would work in MSSQL, dunno about MySQL, but might give you an idea:

DECLARE @Counter int

SELECT @Counter = MAX(COL2) FROM MYTABLE

UPDATE MYTABLE
SET @Counter = @Counter + 1,
    COL1 = @Counter
WHERE ID IN (1,2,3,4,5)

EDIT: I think the SET statement can be simplified in MSSQL to:

SET @Counter = COL1 = @Counter + 1

Answer 4

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 5

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.