Menu
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?
346
The simplest way for creating a sequence in MySQL is by defining the column as AUTO_INCREMENT during table creation, which should be a primary key column.
To number rows in a result set, you have to use an SQL window function called ROW_NUMBER() . This function assigns a sequential integer number to each result row.
A sequence is a set of integers 1, 2, 3, ... that are generated in order on a specific demand. Sequences are frequently used in the databases because many applications require each row in a table to contain a unique value and sequences provide an easy way to generate them.
NEXTVAL returns the next sequence number, and CURRVAL returns the current sequence number. NEXTVAL(N) returns n unique sequence numbers. NEXTVAL(N) can be used only in select sequence.
Run the following queries to have incremented value in yourField column:
SELECT @i:=0; UPDATE yourTable SET yourField = @i:=@i+1; 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
