SQL: update statement with dynamic column value assignment

Question

Submit 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 complete, MAX (COL2) is 1.

My intention is that for an update where ID = 1 COL2 is updated to 'max (COL2) + 1' (i.e. 2), and for subsequent updates, MAX (COL2) + 1 'is overridden, so 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 for MAX (COL2) +1 to be “revised” with every update? I understand that this may cause performance problems, but I'm curious no less! Is there a better alternative (which does not include multiple update statements)?

BTW: you might think about the syntax used for the above query (nested inner table), see here: SQL: using the target table in the UPDATE statement in the nested FROM clause

Answer 1

UPDATE mytable, (
  SELECT @loop := MAX(col1)
  FROM
    mytable
  ) o
SET col1 = (@loop := @loop + 1)

What you met here is called query stability.

The request does not show the changes made by himself, or the following request:

UPDATE mytable
SET col1 = col2 + 1
WHERE col1 > col2 

will never end.

Answer 2

Here's how I 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 variable first @maxonly because I believe that the @Quassnoi trick makes this in a Cartesian product unnecessary and less readable.

, MySQL ORDER BY UPDATE ( SQL), , , . MySQL .

Answer 3

declare @loop int
select @loop = 1

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

Answer 4

, MAX (COL1) +1 "" ?

: (

MSSQL, dunno MySQL, :

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: , SET MSSQL:

SET @Counter = COL1 = @Counter + 1

Answer 5

; -, COL2 . , MAX (COL 1)? , insert update ( ), .

---

, , . , .

ID IN (1,2,3,4,5) . , 5 , WHERE WHERE ID = 1 ( 2, 3, 4, 5 ).