How would I update a column using a
CASE expression where the inner
THEN clause needs to calculate a value based on that same column of other rows in the same table?
i.e. Here's a
list table with six children ordered by
position in ascending order.
CREATE TABLE list ( id int unsigned NOT NULL AUTO_INCREMENT, parent_id int unsigned, position int unsigned, PRIMARY KEY(id) ); -- parents INSERT INTO list (position) VALUES (0),(1); -- parent 1 children INSERT INTO list (parent_id, position) VALUES (1,0), (1,1), (1,2), (1,3), (1,4), (1,5); -- parent 2 children INSERT INTO list (parent_id, position) VALUES (2,0), (2,1), (2,2), (2,3), (2,4), (2,5);
I'm trying to move one of the children to another parent. I need to adjust the old sibling positions to close the gap and set the position of the moving item to be last in the new parent's children (last being the one with the highest
For the reparenting operation, I have the below query. The subquery here seems to be ignored and the moving item's position isn't changed. I've tried other subqueries and functions like
COUNT(position) and even CTEs. Most other options I've tried gave errors though.
-- move row 11 as a child of row 1, readjusting row 2's children UPDATE list AS l1 JOIN list AS l2 ON l1.parent_id = l2.parent_id AND l1.position > l2.position AND l2.id = 11 SET l1.position = (CASE -- reposition old siblings WHEN l1.parent_id = l2.parent_id AND l1.position > l2.position THEN l1.position - 1 -- reposition self to position +1 more than row 1's highest child WHEN l1.id = 122 THEN (SELECT position FROM steps WHERE parent_id = 1 ORDER BY position DESC LIMIT 1) END), l2.parent_id = (CASE WHEN l2.id = 11 THEN 1 END);
I'm trying to do this all in one query (unless such a query would be terribly inefficient). The above query is the only way I've found to update both the
parent_id for only the items that need those changes. The way it's set up, I think the
l2.parent_id = part is being ignored by the sibling items, which is desired and seems like it would be efficient.