Nested Sets: Moving Node in Single UPDATE Query

If you wandered here, you probably know the problem: moving nodes is an expensive and very tricky task, especially when racing conditions are expected; you should lock table, deal with sofisticated transactions (theoretically all rows are affected - probably the best is to SELECT * FROM tblname WHERE 1 to block all rows, thus emulating table locking), or you should write a stored procedure.

Workaround in MySQL is simple: do everyting in one UPDATE query. To cut a long story short:

--- here's the query ---
--- moving as first child of parnet ---
UPDATE `sitemap_items` AS `t0`
JOIN `sitemap_items` AS `object` ON `object`.`id`=25
JOIN `sitemap_items` AS `parent` ON `parent`.`id`=44
SET
`t0`.`lft` = `t0`.`lft` +
	IF (`parent`.`lft` < `object`.`lft`,
		 IF (`t0`.`lft` >= `object`.`rgt` + 1, 0,
				IF (`t0`.`lft` >= `object`.`lft`, `parent`.`lft` - `object`.`lft` + 1,
						IF (`t0`.`lft` >= `parent`.`lft` + 1, `object`.`rgt` - `object`.`lft` + 1 , 0
							)
					)
			 ),
		 IF (`t0`.`lft` >= `parent`.`lft` + 1, 0,
				IF (`t0`.`lft` >= `object`.`rgt` + 1, -`object`.`rgt` + `object`.`lft` - 1,
						IF (`t0`.`lft` >= `object`.`lft`, `parent`.`lft` - `object`.`rgt`, 0
							)
					)
			 )
		),
`t0`.`rgt` = `t0`.`rgt` +
	IF (`parent`.`lft` < `object`.`lft`,
		 IF (`t0`.`rgt` >= `object`.`rgt` + 1, 0,
				IF (`t0`.`rgt` >= `object`.`lft`, `parent`.`lft` - `object`.`lft` + 1,
						IF (`t0`.`rgt` >= `parent`.`lft` + 1, `object`.`rgt` - `object`.`lft` + 1 , 0
							)
					)
			 ),
		 IF (`t0`.`rgt` >= `parent`.`lft` + 1, 0,
				IF (`t0`.`rgt` >= `object`.`rgt` + 1, -`object`.`rgt` + `object`.`lft` - 1,
						IF (`t0`.`rgt` >= `object`.`lft`, `parent`.`lft` - `object`.`rgt`, 0
							)
					)
			 )
		)
WHERE `parent`.`lft` < `object`.`lft` OR `parent`.`lft` > `object`.`rgt`;

Here we abuse the fact that MySQL UPDATE syntax allows thr use of JOINs, so we join target and parent objects' records on each row to calculate left and right fields shifting. WHERE condition at the bottom ensures that parent is not a child of target object (or in fact didn't become it meanwhile - surelly you would check that before executing), making query safe for that racing condition.

If you need to move node next to other one under same parent, use `prev` instead of `parent` and replace all `parent`.`lft` with `prev`.`rgt`, that would do the trick.