Subquery with update/set is failing

Any idea as to why this is not working? Error near ‘From’

update posts AS ordered_posts
set position = sub_query.new_position 
FROM (
  select 
   p.id AS post_id, 
   ROW_NUMBER() over (order by p.position ASC) AS new_position 
   FROM posts p
  ) AS sub_query
WHERE ordered_posts.id = sub_query.post_id;

Thanks in advance

Answer

You are not using MySQL’s update join syntax, which should be this:

UPDATE posts AS ordered_posts
INNER JOIN
(
    SELECT p.id AS post_id, 
           ROW_NUMBER() OVER (ORDER BY p.position) AS new_position 
           FROM posts p
) AS sub_query
    ON ordered_posts.id = sub_query.post_id
SET
    position = sub_query.new_position;

However, I prefer actually not doing this update. Instead, when you want to view your ID sequence in a certain order, just run the subquery:

SELECT p.id AS post_id, 
       ROW_NUMBER() OVER (ORDER BY p.position) AS new_position 
FROM posts p;