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

Please include the rest of the update statement and an explanation of what you are trying to do. — P.Salmon, Nov 20 '20 at 16:16

score 0 · Answer 1 · answered Nov 20 '20 at 14:58

0

Your nested query is selecting from 'gp' but is then aliased as 'gp' it can't select from itself.

answered Nov 20 '20 at 14:58

Ethan Graybeal

96
5

forpas · Accepted Answer · 2020-11-20T16:39:51.923

0

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.

edited Nov 20 '20 at 16:39

answered Nov 20 '20 at 16:32

forpas

160,666
10
38
76

Update with nested select in from statement does not work mysql

2 Answers2