How to write a valid query (update with autoincrement variable) ?

Question

I want to write query like this:

SET @id := 0;
UPDATE table1
SET
    field1 = @id + 1,
    @id := @id + 1

And get error message:

#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near '@id := @id + 1' at line 3 

How write this query correctly?

10x thx for your help

Answer 1

What you need is a sequence. You could use this implementation for example:

INSERT INTO seq VALUES('s1',1);
UPDATE table1 SET field1 = seq('s1');

Answer 2

You can do it like this, with the assignment and increment combined:

SET @id := 0;
UPDATE table1
SET
    field1 = (@id := @id + 1)

EDIT: you can even use an ORDER BY on such an UPDATE query to specify the order in which the numbers should be assigned.

Answer 3

You simply need to break this into three statements instead of two.

SET @id := 0;
UPDATE table1
SET field1 = @id + 1;
SET @id := @id + 1;