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.
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 🙂