Subquery with update/set is failing

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply