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;
UPDATE
alternative way with one statement
UPDATE T
JOIN (SELECT @rank := 0) r
SET Number=@rank:=@rank+1;
ORDER BY NAME to that update if required :)
ORDER BY because it's a multi-table update. This answer gets around the issue, though.
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:
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.
Using above two solutions I made this query and it worked.
SET @rank=0;update emp_data set empfk_id = @rank:=@rank+1;
update `table_name` set `Number`=@rank:=@rank+1
This will be done. Thanks
