Update column enumerating row number in MySQL 5.7

Question

I am stuck at a query in MySQL 5.7. Simplified, i have a temporary table like this:

guid  B   C
 2   aa  55
 5   ab  66
 7   ac  77
 9   ad  88

What i want to do is, turn the column guid into an enumeration starting from 1, while not touching any data in the other columns:

guid  B   C
 1   aa  55
 2   ab  66
 3   ac  77
 4   ad  88

I have been looking for solutions involving ROW_NUMBER and ROW_ID but my syntax won't be accepted:

UPDATE temp_table
SET temp_table.guid = temp_table.New_guid
FROM (
      SELECT guid, ROW_NUMBER() OVER (ORDER BY [guid]) AS New_guid
      FROM temp_table
      ) x

I tried to adopt the above from this question, but it keeps sayng "FROM is not a valid input at this position".

score 5 · Accepted Answer · answered Feb 25 '21 at 21:18

You can use variables:

set @rn = 0;

update temp_table
    set guid = (@rn := @rn + 1)
    order by guid;

If you don't actually care about the current ordering you can use:

update temp_table tt cross join
       (select @rn := 0) params
    set tt.guid = (@rn := @rn + 1);

Unfortunately, you cannot use order by when the update has a join.

Update column enumerating row number in MySQL 5.7

1 Answers1