Adding incremented value to new column

Question

i am trying to add incremented values to a new column in table. Here is a sample structure of table

---------------------
Name - class - id
---------------------
abbc - 2     - null
efg  - 4     - null
ggh  - 6     - null
---------------------

i want to write a query that will generate unique id's for all records in table Here is the query i have tried but show null

set @i=0;
update table1 set id =(@i:=@i+1);

What you have should work, I tested on my database, testcase is in my answer. The most reasonable explanation is that @i is not initialized when the UPDATE statement runs (SET performed in a different database session?) When you say it "show null", you mean a SELECT in the same session returns NULL for id? Does the UPDATE statement return a number of rows affected, or does it return an error? What you have shown here should work; there's something going on that we're not seeing here (an update trigger, an error, constraint violation, transaction rolled back, multiple sessions, et al.) — spencer7593, Feb 24 '14 at 05:46

score 3 · Accepted Answer · answered Feb 24 '14 at 05:40

What you have shown should work; the id column should be getting assigned values.

I tested your statement; I verified it works on my database. Here's the test case I ran:

CREATE TABLE table1 (`name` VARCHAR(4), class TINYINT, id INT);
INSERT INTO table1 (`name`,class) VALUES ('abbc',2),('efg',4),('ggh',6);
SET @i=0;
UPDATE table1 SET id =(@i:=@i+1);
SELECT * FROM table1;

Note that MySQL user variables are specific to a database session. If the SET is running in one session, and the UPDATE is running another session, that would explain the behavior you are seeing. (You didn't mention what client you ran the statements from; most clients reuse the same connection, and don't churn connections for each statement, I'm just throwing that out as a possibility.)

To insure that @i variable is actually initialized when the UPDATE statement runs, you can do the initialization in the UPDATE statement by doing something like this:

UPDATE table1 t
 CROSS
  JOIN (SELECT @i := 0) s
   SET t.id =(@i:=@i+1);

I tested that, and that also works on my database.

score 1 · Answer 2 · answered Feb 24 '14 at 05:20

1

try this query my friend:

set @i=0;
update table1 set id =(select @i:=@i+1);

SQL Fiddle

answered Feb 24 '14 at 05:20

Hamidreza

3,038
1
18
15

it seems to be working fine but in my case its returning NULL in that column n, the id column is of simple integer type, there are 4 Fkeys but no primary key in the table. – AddyProg Feb 24 '14 at 05:33
it is just a guess but have you ever try to `LIMIT` your rows and then update them? – Hamidreza Feb 24 '14 at 05:42
no i didn't, but answer from spencer7593 worked for me. but thanks you for ur answer it was rite one too, but not in my case :-) – AddyProg Feb 24 '14 at 05:47

score 0 · Answer 3 · answered Feb 24 '14 at 05:24

0

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

answered Feb 24 '14 at 05:24

124

2,757
26
37

score -1 · Answer 4 · answered Feb 24 '14 at 05:16

-1

Use AUTOINCREMENT parameter for the respective column instead. This parameter will put an unique auto incremented value in the respective column.

answered Feb 24 '14 at 05:16

user3227262

563
1
6
16

there are round about 50,000 records already present in respective table , try to understand the question first.. – AddyProg Feb 24 '14 at 05:19
1

@AdilWaqar: you no where mentioned that there are 50000 records already present in tables. Please explain your question properly. – Deepak Rai Feb 24 '14 at 05:24
@Deepak, dear sample table structure clearly shows that there are some records already present in table. it doesn't matter there is one record or 50,000, i have clearly mentioned how i want to do that, as you can clearly judge it from the query i have tried.. Thanks for the comment – AddyProg Feb 24 '14 at 05:30

Adding incremented value to new column

4 Answers4

SQL Fiddle