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 position
).
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 position
and 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.