Update values incrementally in mysql

One field of my table's field is set to 0 for all 开发者_如何学Crows. But I want to update the incremental value by step 1 in an update query.

How can I do that in mysql?

---

Try this:

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

---

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

You can go for this as well.

---

One line solution:

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

---

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

---

The easiest way for me is to drop the field and re-create it with auto-increment.