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
SET @rank:=0; update T set Number=@rank:=@rank+1;
alternative way with one statement
UPDATE T JOIN (SELECT @rank := 0) r SET Number=@rank:=@rank+1;
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
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.