updating columns with a sequence number mysql

I have a table with the columns: (this is only an example I have 50K records)

Name,   Number

Joe     Null
Michael Null
Moses   Null

I to update the number with a sequence number from 1-3 so it will look like this:

Name,   Number

Joe     1
Michael 2
Moses   3

How can I do it in SQL for Mysql in one SQL command

Solution 1:

SET @rank:=0;
update T
set Number=@rank:=@rank+1;

UPDATE

alternative way with one statement

UPDATE T
JOIN (SELECT @rank := 0) r
SET Number=@rank:=@rank+1;

Solution 2:

You could try setting the Number to AUTO_INCREMENT so the numbers will be generated:

ALTER TABLE your_table MODIFY Number INT AUTO_INCREMENT

Other than that, you probably need: a) stored routines b) application code

Solution 3:

I struggled to find an answer online and found the following method worked for me, based on some of the info above but with a different update approach. Posting here in case it helps others too.

Note: for my dataset, the rank is a one-time fixed calculation, it won't change. I wanted to add it as a column to save running a computation each time I pull the data from MySQL into Python.

Solution I used:

• set rank to 0 (as above)
• create a temporary table containing the select query to compute the rank
• update the original table with the computed ranks.

Sample names used, rowid is the unique identifier for each row, value is the metric that will be used to determine the rank for each row

SET @rank=0;
CREATE TEMPORARY TABLE rank_temp
AS
(SELECT rowid, value, @rank:=@rank+1 AS rank FROM source_table ORDER BY value ASC);

UPDATE sourcetable a, rank_temp b
SET a.rank = b.rank
WHERE a.rowid = b.rowid;

It's a crude method but has worked on my dataset.