How to "UPDATE table SET col = (CASE WHEN ... THEN (SELECT col FROM table ORDER BY col DESC LIMIT 1) END)"? 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 &gt; l2.position AND l2.id = 11
  SET l1.position = (CASE
    -- reposition old siblings
    WHEN l1.parent_id = l2.parent_id AND l1.position &gt; 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.
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 &gt; 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

After

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

After