How to batch update data in mysql using their ordered sequence info?

Question

say, i have a table t with only 2 fields, like below

ID    VALUE
---------------------
0      100   
0      103   
0      101   
0      102  
...
...
...
0      110,000,000

i need to update the id fields using their ordered sequence and change the table , like below:

ID    VALUE
---------------------
1      100   
2      101   
3      102   
4      103   
...
...
...
109999901       110,000,000

How do i write the sql script in mysql to make this happen? Seems like in Oracle it's much easier.

Gordon Linoff · Accepted Answer · 2021-04-27 02:17:54Z

1

One method is:

update t join
       (select t.*, row_number() over (order by value) as seqnum
        from t
       ) tt
       on t.value = tt.value
    set t.id = tt.seqnum;

However, a better method might use variables:

set @rn := 0;

update t
    set id = (@rn := @rn + 1)
    order by value;

For these, you would want an index on (value). You would have better performance by just creating a new table, though.

answered Apr 27, 2021 at 2:17

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2021-04-27 02:18:20Z

0

If you are using MySQL 8+, then I suggest actually not doing this update, but instead just use a select along with ROW_NUMBER:

SELECT ROW_NUMBER() OVER (ORDER BY ID), VALUE
FROM yourTable
ORDER BY ID;

You could also put the above select into a view and use that if you have a frequent need to view your data in this way. The reason for not wanting to do an update is that should you insert new ID values which occur in the middle of your current sequence, you might be forced to run the update again. This could get to be a burden after a while.

answered Apr 27, 2021 at 2:18

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

How to batch update data in mysql using their ordered sequence info?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related