1

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.

1

I'm not sure of your reasoning for wanting to do it within a single query, but the one thing I would ask though is that you think about the maintenance of such a query - you may not be the only person who has to support this.

It took me a while to understand what your query was doing, and in the end I ignored it completely and continued based upon the question you were asking. It would have been much simpler, easier to understand and support if you used a single statement for each step and then wrap it in a transaction for consistency. Depending on the number of rows in the table, it might even be more performant as you won't need to join the table to itself a few times.

However the below will achieve what you want:

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);

WITH a AS 
(
    SELECT parent_id, MAX(position) as max_position
      FROM list
  GROUP BY parent_id
)
   UPDATE list AS b
     JOIN a
       ON a.parent_id = b.parent_id
LEFT JOIN list AS c
       ON c.parent_id = b.parent_id
      AND c.position > b.position
      SET b.parent_id = 1
        , b.position = a.max_position + 1
        , c.position = c.position - 1
    WHERE b.id = 11;
 
SELECT * 
  FROM list 
 ORDER BY parent_id, position;

Below are the results when moving a item that is not the last child of a parent.

Before

Before moving non-last child

After

After moving non-last child

Below are the results when moving a item that is the last child of a parent.

Before

Before moving last child

After

After moving last child

6
  • 1
    Thank you for your solution. And, you make good points. Using the XDevAPI connector for Node, I couldn't use its parameter binding to prepare multiple statements. I thought a single query would be good to avoid multiple round trips b/w database and application. Your solution doesn't work though when the row being reparented is the last child of its current parent. Any idea what's happening there? – slanden Jun 14 '20 at 23:22
  • I got the right results from your solution by changing ON a.parent_id = b.parent_id to ON a.parent_id = 1, > to >=, removing b.position = ..., and changing c.position = c.position - 1 to , c.position = (CASE WHEN c.id = b.id THEN a.max_position + 1 ELSE c.position - 1 END). The problem now is the affected row count will always be one more than what it should be... – slanden Jun 15 '20 at 3:29
  • @slanden thanks for the information! I have adjusted the query so that it now works when reparenting the last child. – World Wide DBA Jun 15 '20 at 6:10
  • Thank you, that's so close! But, the reason I used ON a.parent_id = 1 is because the way you have it, when reparenting the last child the position gets set to the max position of the old parent's children. So, If new parent has 6 children and old parent has 4 children, the reparented item's new position gets set to 4, not 6. Maybe it's bad practice to join a table like that, but it works... What's your take on this? – slanden Jun 15 '20 at 22:04
  • @slanden I'm sorry but I disagree. I have posted screenshots of the query working as expected using the base data that you provided. I even tried moving the last child of parent 1 to the last child of parent 2 and it gave me the correct results. – World Wide DBA Jun 15 '20 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.