Hi! Thanks for your reply. Sorry I was using another table which also has left and right using the nested set database.
Here’s the table and the code.
CREATE TABLE IF NOT EXISTS `mediatree` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`parent_id` bigint(20) NOT NULL,
`position` bigint(20) NOT NULL,
`lft` bigint(20) NOT NULL,
`rgt` bigint(20) NOT NULL,
`level` bigint(20) NOT NULL,
`title` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
`type` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
NSERT INTO `mediatree` (`id`, `parent_id`, `position`, `lft`, `rgt`, `level`, `title`, `type`) VALUES
(1, 0, 2, 1, 14, 0, 'ROOT', ''),
(2, 1, 0, 2, 11, 1, 'C:', 'drive'),
(3, 2, 0, 3, 6, 2, '_demo', 'folder'),
(4, 3, 0, 4, 5, 3, 'index.html', 'default'),
(5, 2, 1, 7, 10, 2, '_docs', 'folder'),
(6, 1, 1, 12, 13, 1, 'D:', 'drive'),
(7, 5, 0, 8, 9, 3, 'zmei.html', 'default');
/* Move Node to child of target node */
/* get the parameters for the sub tree to move */
SELECT @mylft := lft, @myrgt := rgt, @myWidth := rgt - lft + 1, @catId := id
FROM mediatree
WHERE id = '7';
/* get the lft and rgt value of the new parent cat */
SELECT @insLft := lft, @insRgt := rgt FROM mediatree WHERE id = '3';
/* get the offset to renumber the subtree lfts and rgts */
SELECT @step := @insLft - @mylft + 1;
/* transfer the subtree to a temp table */
INSERT tblcattemp
SELECT * FROM mediatree
WHERE lft >= @mylft AND lft <= @myrgt;
/* update the temp table - renumber the lfts and rgts and make the catId neg temporarily */
UPDATE tblcattemp
SET lft = lft + @step,
rgt = rgt + @step,
id = -id;
/* update the rest of the tree to the rgt of the move point*/
UPDATE mediatree SET lft = lft + @myWidth WHERE lft >= @insRgt;
UPDATE mediatree SET rgt = rgt + @myWidth WHERE rgt >= @insRgt;
/* insert the sub tree in the temp table */
INSERT mediatree
SELECT * FROM tblcattemp;
/* delete the original subtree */
SELECT @mylft := lft, @myrgt := rgt, @myWidth := rgt - lft + 1
FROM mediatree
WHERE id = @catId;
DELETE FROM mediatree WHERE lft BETWEEN @mylft AND @myrgt;
UPDATE mediatree SET rgt = rgt - @myWidth WHERE rgt > @myrgt;
UPDATE mediatree SET lft = lft - @myWidth WHERE lft > @myrgt;
/* reset neg. catId's to pos. and clean up tblcattemp*/
UPDATE mediatree SET id = -id WHERE id < 0;
DELETE FROM tblcattemp;
tblcattemp is another empty table with the same structure as mediatree.