Update values incrementally in mysql

One field of my table's field is set to 0 for all rows. But I want to update the incremental value by step 1 in an update query.

How can I do that in mysql?

Solution 1:

Try this:

mysql> select @i := 0;
mysql> update bar set c = (select @i := @i + 1);

Solution 2:

SET @a = 0;  
UPDATE customers SET id = @a:=@a+1;

You can go for this as well.

Solution 3:

One way is to create a new table with an AUTO_INCREMENT column instead of the original column, inserting all data from the old into the new table, and then renaming the new and deleting the old.

Another way is to run your update query with a MySQL variable that generates an increasing number for each row (to emulate the ROW_NUMBER() function found in other DBMS systems).

Solution 4:

One line solution:

UPDATE tablename AS a, (SELECT @a := 0) AS b SET a.fieldname = @a:=@a+1