updating columns with a sequence number mysql
I have a table with the columns: (this is only a开发者_如何学编程n 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;
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.
update `table_name` set `Number`=@rank:=@rank+1
This will be done. Thanks
Using above two solutions I made this query and it worked.
SET @rank=0;update emp_data set empfk_id = @rank:=@rank+1;
精彩评论