Suppose we have a table with rows representing nodes of a tree structure, each node being represented by an id, a parent id and a position between the children of its parent node:
CREATE TABLE `tree`.`node` (
`idnode` TINYINT NOT NULL AUTO_INCREMENT ,
`idnode_parent` TINYINT NULL ,
`position` TINYINT NULL ,
PRIMARY KEY ( `idnode` )
);
The nodes with idnode_parent set to 0 or NULL are on the first level of the tree structure.
Now we populate the table:
#add some first level nodes
INSERT INTO `tree`.`node` VALUES (NULL, 0, 1); #idnode = 1
INSERT INTO `tree`.`node` VALUES (NULL, 0, 2); #idnode = 2
#add some children for the first node on the first level
INSERT INTO `tree`.`node` VALUES (NULL, 1, 1); #idnode = 3
INSERT INTO `tree`.`node` VALUES (NULL, 1, 2); #idnode = 4
#add some children for the node with idnode 2
INSERT INTO `tree`.`node` VALUES (NULL, 2, 1); #idnode = 5
INSERT INTO `tree`.`node` VALUES (NULL, 2, 2); #idnode = 6
INSERT INTO `tree`.`node` VALUES (NULL, 2, 3); #idnode = 7
#add a child for the node with idnode 7
INSERT INTO `tree`.`node` VALUES (NULL, 7, 1); #idnode = 8
Now the table looks like this:
idnode idnode_parent position
1 0 1
2 0 2
3 1 1
4 1 2
5 2 1
6 2 2
7 2 3
8 3 1
9 7 1
We want to delete:
– node 1, so we move its children (3, 4) up one level (idnode_parent changes from 1 to 0) and update their positions to the position of their parent (position changes from 1, 2 respectively to 1);
– node 7, so we move its children (8) up one level (idnode_parent changes from 7 to 2) and update their positions to the position of their parent (position changes from 1 to 3)
a) We try this query:
UPDATE node
SET idnode_parent = CASE(idnode_parent)
WHEN 1 THEN 0
WHEN 7 THEN 2
END,
position = CASE(idnode_parent)
WHEN 1 THEN 1
WHEN 7 THEN 3
END
WHERE idnode_parent IN (1, 7);
Running this query, MySQL says 3 rows were affectedand the table looks as follows:
idnode idnode_parent position
1 0 1
2 0 2
3 0 NULL
4 0 NULL
5 2 1
6 2 2
7 2 3
8 3 1
9 2 NULL
We can see that only the field idnode_parent updated as expected, the position field for the moved nodes being set to NULL.
b) We reset the table to the initial test data and we run the above query with the difference of using the ELSE clause in the CASE expression:
UPDATE node
SET idnode_parent = CASE(idnode_parent)
WHEN 1 THEN 0
WHEN 7 THEN 2
ELSE idnode_parent
END,
position = CASE(idnode_parent)
WHEN 1 THEN 1
WHEN 7 THEN 3
ELSE position
END
WHERE idnode_parent IN (1, 7);
The result is:
idnode idnode_parent position
1 0 1
2 0 2
3 0 1
4 0 2
5 2 1
6 2 2
7 2 3
8 3 1
9 2 1
So the idnode_parent field updates correctly, but position remains unchanged.
My conclusion is that that the query runs as if there were two different queries running over the bunch of rows resulted after evaluating the WHERE condition, the second CASE operator applying over the data modified by the first CASE operator:
1. WHERE condition is evaluated; resulted data set:
idnode idnode_parent position
3 1 1
4 1 2
9 7 1
2. idnode_parent is set by evaluating the first CASE expression:
idnode idnode_parent position
3 0 1
4 0 2
9 2 1
3. position is set by evaluating the second CASE expression:
a) Using the query without the ELSE condition, position is set to NULL for all the rows in the data set, as after running the first CASE expression there are no more rows with the idnode_parent needed for the second CASE expression:
idnode idnode_parent position
3 0 NULL
4 0 NULL
9 2 NULL
b) Using the query with the ELSE condition, position remains unchanged for all the rows in the data set, as there are no more rows with the needed idnode_parent, as they were modified by applying the previous CASE operator, and the ELSE condition is evaluated:
idnode idnode_parent position
3 0 1
4 0 2
9 2 1
So if we want the query to run correctly, we set the position first, and then the id of the parent node, as position does not modify data needed for subsequent idnode_parent updates in the same query:
UPDATE node
SET position = CASE(idnode_parent)
WHEN 1 THEN 1
WHEN 7 THEN 3
END,
idnode_parent = CASE(idnode_parent)
WHEN 1 THEN 0
WHEN 7 THEN 2
END
WHERE idnode_parent IN (1, 7);
We finally obtain the expected result:
idnode idnode_parent position
3 0 1
4 0 1
9 2 3