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?
1
-
Update them with row num, you can find examples in SOJahan Zinedine– Jahan Zinedine2012-01-14 12:40:54 +00:00Commented Jan 14, 2012 at 12:40
Add a comment
|
3 Answers
Run the following queries to have incremented value in yourField column:
SELECT @i:=0;
UPDATE yourTable SET yourField = @i:=@i+1;
3 Comments
Zbynek
Worth noting that first inserted value is 1, not 0
Alexander van Oostenrijk
For very large tables, you may run out of InnoDB locks. Increase
@@innodb_buffer_pool_size to accommodate.
mirapole
Advice from new MySQL versions like 8:
Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s).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
Comments
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
