CODEWITHSUNDEEP
mysqlsql
Rendicahya
Rendicahya

Reputation: 4285

Insert sequential number in MySQL

I added an empty column to a table and now I want to insert sequential numbers to its rows. Is it possible to do it using SQL?

Upvotes: 32

Views: 33209

Answers (3)

dfsq
dfsq

Reputation: 193291

Run the following queries to have incremented value in yourField column:

SELECT @i:=0;
UPDATE yourTable SET yourField = @i:=@i+1;

Upvotes: 75

Jahan Zinedine
Jahan Zinedine

Reputation: 14874

As I said in comments you can update every row with its row number,

Here is a link to how to calculate rownum in MySQL.

To rephrase:

update player,
       (select @rownum:=@rownum+1 ‘rank’, p.* 
        from player p,
        (SELECT @rownum:=0) r 
        order by score desc) player1
 set thatColumn= rank
 where player.id = player1.id

Upvotes: 1

PresleyDias
PresleyDias

Reputation: 3711

try this auto increment if you wanted to have incremental number in your table for each insert that you do

create table WithAutoInc(somID int AUTO_INCREMENT,somName_ char(100) ,primary key(somID ));

now to insert you can do this

 insert into WithAutoInc (somName_) values ('presley');

the result is

enter image description here

Upvotes: 0

Related Questions