Update values incrementally in mysql

Question

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?

To clarify: You are asking how to change an existing data set so it has incrementing numbers? — Pekka, Dec 14 '10 at 12:13
no,i add this field recently so it is set to 0 by default and it should not be auto increment for feature.i just want to fill it with valid data.hope i could clarify it. — hd., Dec 14 '10 at 12:16

score 121 · Accepted Answer · answered Dec 14 '10 at 12:18

121

Try this:

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

answered Dec 14 '10 at 12:18

Pablo Santa Cruz

176,835
32
241
292

score 16 · Answer 2 · answered Jan 27 '17 at 06:59

16

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

You can go for this as well.

answered Jan 27 '17 at 06:59

DRUPWAY

345
6
16

1

can you please tell me how i can implement this in php ? – Rahul Sharma May 10 '17 at 10:25
Worked fine. Thank You SushilKumar, your suggestion was easy and effective. – Samuel Ramzan Aug 16 '18 at 22:00
any way to fix the order of the table? – Flying Turtle Feb 11 '21 at 23:39
@FlyingTurtle add an ORDER BY to the UPDATE statement – Adrian Günter Sep 13 '21 at 14:57

score 7 · Answer 3 · answered Nov 15 '21 at 11:23

7

One line solution:

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

answered Nov 15 '21 at 11:23

mjjm

73
1
3

score 3 · Answer 4 · answered Dec 14 '10 at 12:17

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

answered Dec 14 '10 at 12:17

littlegreen

7,290
9
45
51

as i remember i did it before using mysql variables but i don't remember the solution at this time. ;( – hd. Dec 14 '10 at 12:19

score -2 · Answer 5 · answered Feb 21 '21 at 07:34

-2

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

answered Feb 21 '21 at 07:34

Paul Godard

1,055
1
14
30

Update values incrementally in mysql

5 Answers5