Update with nested select in from statement does not work mysql

Question

Here is an error I don't understand:

     mysql> UPDATE gp
    -> SET gp.gpid = gp.new_gpid
    -> FROM (
    ->       SELECT gpid, ROW_NUMBER() OVER (ORDER BY [gpid]) AS new_gpid
    ->       FROM gp
    ->       ) gp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (
      SELECT gpid, ROW_NUMBER() OVER (ORDER BY [gpid]) AS new_gpid
      ' at line 3

As far as I can tell nested SELECT in a FROM statement seems to be depreciated.

Am using mysql 8.0.21

Any help to make this query work would be greatly appreciated.

Thank you

EDIT 1: What I am trying to achieve is to update the gpid column with row numbers instead of the actual AUTO_INCREMENT id, which contains gaps in between ids, the explanation in this post Change mysql auto increment id column value

Answer 1

The UPDATE... FROM ... syntax is not supported by MySql.
You can do it with a JOIN:

UPDATE gp 
INNER JOIN (
  SELECT gpid, ROW_NUMBER() OVER (ORDER BY gpid) AS new_gpid
  FROM gp
) t ON t.gpid = gp.gpid
SET gp.gpid = t.new_gpid; 

See a simplified demo.