Nested Sets: move subtree

There are several multi-statement solutions out there to move a subtree to a new location (possibly inside another parent node), but finding a solution on the Web to achieve this with a single update statement was anything but successful.

Well, here is one, primarily designed for and thoroughly tested on MySQL and MariaDB. It updates the lft and rgt columns of the nodes involved directly without the need for locks or transactions.

-- moves a subtree before the specified position
-- if the position is the rgt of a node, the subtree will be its last child
-- if the position is the lft of a node, the subtree will be inserted before
-- @param l the lft of the subtree to move
-- @param r the rgt of the subtree to move
-- @param p the position to move the subtree before
update tree
set
	lft = lft + if (:p > :r,
		if (:r < lft and lft < :p,
			:l - :r - 1,
			if (:l <= lft and lft < :r,
				:p - :r - 1,
				0
			)
		),
		if (:p <= lft and lft < :l,
			:r - :l + 1,
			if (:l <= lft and lft < :r,
				:p - :l,
				0
			)
		)
	),
	rgt = rgt + if (:p > :r,
		if (:r < rgt and rgt < :p,
			:l - :r - 1,
			if (:l < rgt and rgt <= :r,
				:p - :r - 1,
				0
			)
		),
		if (:p <= rgt and rgt < :l,
			:r - :l + 1,
			if (:l < rgt and rgt <= :r,
				:p - :l,
				0
			)
		)
	)
where :r < :p or :p < :l;

Sometimes it's easier to swap two entire subtrees instead of moving them around. So here is another single-statement update that performs a direct update of the lft and rgt columns of two non-overlapping subtrees, so that each will occupy the other's position.

-- swaps two subtrees, where A is the subtree having the lower lgt/rgt values
-- and B is the subtree having the higher ones
-- @param al the lft of subtree A
-- @param ar the rgt of subtree A, must be lower than bl
-- @param bl the lft of subtree B, must be higher than ar
-- @param br the rgt of subtree B
update tree
set
	lft = lft + @offset := if (lft > :ar and rgt < :bl,
		:br - :bl - :ar + :al,
		if (lft < :bl, :br - :ar, :al - :bl)
	),
	rgt = rgt + @offset
where lft >= :al and lft <= :br and :ar < :bl;

The drawback of these statements is that lft and rgt values are temporarily ambiguous while the statement is being executed, so this method can only be applied when there is no unique index on the lft and rgt column. Although in the end there will be no unique key violations at all, MySQL would complain about them occuring during the update process.