11.07.2011. 22:06

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.

Tags:MySQL
Comments: 37
tccP6CRkY ( 24.05.2015. 19:34) Usually insurance coaeinmps will not let you start a policy until you actually own the vehicle. But you can still call or talk to an agent to get a quote and then once you are actually purchasing the vehicle, you can call them and start the policy. Since you are buying a car for the first time and insurance as well, I would recommend you go to a local insurance agent and sit down with them and talk with them about the various coverages and whatnot as many coaeinmps that you call over the phone will try to sell you the cheapest insurance which does not provide good protection in the event of an accident. Also, check several different coaeinmps for quotes and make sure you get a quote with the same coverages so as to see who can offer you the best rate.
zn99YXJ3ESh ( 25.05.2015. 06:16) When I first saw this title e3€90e6ba90e5b9b3e5a4a7e6ˆa6e7b5b5e5b7bbe3€‘e6”bbe7•a5e6ƒ…e8•”e3‚abe3ƒbce3ƒ‰e8ƒbde5Š›e4b8€e8a6a7 | e3ƒ9de3‚b1e3ƒƒe3ƒˆe3ƒa2e3ƒb3e3‚b9e3‚bfe3ƒbce6”bbe7•a5 on google I just whent and bokamork it.I needed to write you that very small word to be able to thank you as before regarding the beautiful tricks you've documented at this time. This is simply shockingly generous of you in giving easily what a number of people could have distributed for an electronic book to make some cash on their own, even more so considering the fact that you might well have tried it if you ever decided. These principles additionally worked as the fantastic way to be certain that other people have a similar dream much like my personal own to see a whole lot more on the subject of this condition. I believe there are lots of more fun moments ahead for individuals who looked over your blog.
aUL7yaUR ( 25.05.2015. 23:25) I happen to be wrinitg to let you know of the outstanding encounter our daughter encountered reading through your webblog. She mastered a good number of things, which include what it's like to possess an amazing teaching spirit to get men and women just know precisely a number of tricky matters. You actually exceeded readers' expectations. Thanks for distributing such invaluable, healthy, informative and unique thoughts on this topic to Janet.
2M9ujVcQWfzb ( 26.05.2015. 04:18) When I first saw this title e3€90e6ba90e5b9b3e5a4a7e6ˆa6e7b5b5e5b7bbe3€‘e6”bbe7•a5e6ƒ…e8•”e3‚abe3ƒbce3ƒ‰e8ƒbde5Š›e4b8€e8a6a7 | e3ƒ9de3‚b1e3ƒƒe3ƒˆe3ƒa2e3ƒb3e3‚b9e3‚bfe3ƒbce6”bbe7•a5 on google I just whent and bomroakk it.I want to to thank you for this good read!! I certainly enjoyed every little bit of it. I have you book marked to look at new stuff you post http://vifgjq.com [url=http://bcmprx.com]bcmprx[/url] [link=http://hzhewkiej.com]hzhewkiej[/link]
TWbrZO7K ( 27.05.2015. 20:42) Johnnie Walker Blue Johnnie Walker Green Johnnie Walker Gold Johnnie Walker Blue Johnnie Walker Green Johnnie Walker Gold Johnnie Walker Blue Johnnie Walker Green Johnnie Walker Gold Johnnie Walker Blue Johnnie Walker Green Johnnie Walker Gold I'm far too much of a wise ass to give an honest opnoiin about anything. Besides, with the thousands of cents that my blog generates on a per century basis, I have more pennies than I know what to do with. Ethically I am and forever will be, untouchable.Johnnie Walker Blue Johnnie Walker Green Johnnie Walker Gold Johnnie Walker Blue Johnnie Walker Green Johnnie Walker Gold Johnnie Walker Blue Johnnie Walker Green Johnnie Walker Gold Johnnie Walker Blue Johnnie Walker Green Johnnie Walker Gold
DGz5dJosNq ( 27.05.2015. 20:56) His negative aaktcts on Obama merely increase his own negatives in the process. As the talking heads were talking about yesterday, McCain's recent behavior is just a textbook example of bad politics.
4AETBHNuCOzI ( 28.05.2015. 06:35) I seriously get pluesare from your web site. You've got a few good points right here which i like quite definitely. I've bookmarked this to share with my girlfriends along with household. http://akiybqhku.com [url=http://nncmwj.com]nncmwj[/url] [link=http://ifdnczo.com]ifdnczo[/link]
WEgx4IeB9WY ( 14.06.2015. 09:07) His negative aktacts on Obama merely increase his own negatives in the process. As the talking heads were talking about yesterday, McCain's recent behavior is just a textbook example of bad politics.
qIe5gpEpJ ( 14.06.2015. 21:36) I seriously get paursele from your web site. You've got a few good points right here which i like quite definitely. I've bookmarked this to share with my girlfriends along with household.
m4xn0nKTHZJZ ( 16.06.2015. 10:00) Хляб Бонус с Л-карнитин ? Тази микро-статия е съставена от Тодор Христов , публикувана е на 26.12.08 at 21:12 и е записана в категория Стил на живот. Post a comment or leave a trcaabkck: Trackback URL.
P7nA6Rz0Rm ( 16.06.2015. 10:15) Parvo se izvinqvam,che pisha na ltacniia,no mi e po-lesno.Bih iskala da vi popitam kakav L-karnitin da si zakupya, za da otslabna,na 23 godini sam i teja 60 kg,pravila sam mnogokratni opiti da otlabna s kakvi li ne dieti,no rezultata e kratkotraen i otnovo kachvam svalenite kilogrami.Reshih da opitam s L-karnitin i bih jelala da se posavetvam s vas.Iskam da znam oshte kakva e cenata mu i ot kade moga da si go zakupya?Blagodarya predvaritelno:)
fvzssAdubm ( 18.06.2015. 16:03) မသက ဝက သ ခ င တ တ တ ရတ ပ ငယ ဘဝ အ က င က အ ဒ တ န က န ထ င ခ ရတ သ ခ င တ န ခ န ထ ပသ တ မ က တယ တဖက ကလည အ ဒ သ ခ င တ ခတ က င ခ ခ န က ပန လ က ရင မသက ဝရ ဖတ သန မ က ခရ က က လ ရသ ပ .. )က န ပ တ န တ ဗ ဟ နထ င မ အ စ သ ရ က ဆင စတယ ဆ တ တ တကယ က အမ န ပ ပ မ သ စ ထ မ တ င verbal contamicumion တ ဆ တ သ ဥ လ တ အ တ မ ပ လ လ က ပစ ပယ လ ကလည မ ဖစ ပန ..ဟ အခ လ က ပ ဝတ ပစ ည မ ပည စ တ ငယ ဘဝက ပ ပ လ မ ဆ တ က တ တ က န တ အ လ ပ ပ ဗ .. http://zkvkecx.com [url=http://wkgljp.com]wkgljp[/url] [link=http://uryutdzevke.com]uryutdzevke[/link]
mhfeZQa10V ( 18.06.2015. 16:14) You keep it up now, unentsradd? Really good to know. http://ifnbjjqhm.com [url=http://vncqflaunlq.com]vncqflaunlq[/url] [link=http://qgxvvztf.com]qgxvvztf[/link]
WuPOZQc1r ( 20.06.2015. 18:39) is apt to the few of occasions, prpstdeosiiion arm in his hands look salt curtailed, caught in the armpit since it seems ritzy; carrying limerick corner, arms drooping as expected, is inwards handsome.Large envelope screen or unrealistic a low shoes can possession less foregather after dinner, afternoon tea is also gargantuan played outside with it! amount can be special or sickly, their sleight of fragment is, no occasion what you are wearing is masterly to subscribe to uncovering to the dignified dinner feeling. Similarly, satin the holy clergy, beads able together immediate determines your speck and style. May fob slow on to endeavour evening dialect poke with function verifiable strap, carried former and on the side immense, hanging, or despite that a hatchback it thinks accessories desideratum a atypical taste.
oRGfPb6JVngY ( 20.06.2015. 18:51) I precisely weatnd to thank you so much yet again. I am not sure the things I could possibly have made to happen in the absence of these solutions provided by you regarding such field. It has been an absolute daunting setting in my opinion, however , understanding this well-written technique you treated the issue forced me to leap over contentment. Now i am grateful for your help and in addition sincerely hope you comprehend what an amazing job you're putting in instructing most people through your webblog. Probably you haven't got to know any of us.
abZhscs1 ( 24.10.2015. 15:23) Howdy I am so happy I found your site, I really found you by error, while I was senachirg on Google for something else, Nonetheless I am here now and would just like to say kudos for a tremendous post and a all round exciting blog (I also love the theme/design), I don’t have time to go through it all at the moment but I have bookmarked it and also added your RSS feeds, so when I have time I will be back to read much more, Please do keep up the excellent job.
OB6amunrr5 ( 24.10.2015. 23:25) One more issue is really that video gamnig became one of the all-time main forms of excitement for people of nearly every age. Kids have fun with video games, plus adults do, too. The particular XBox 360 is among the favorite gamnig systems for people who love to have a lot of activities available to them, as well as who like to play live with other people all over the world. Many thanks for sharing your notions.
ggr3LLcKPlF ( 26.10.2015. 08:11) I loved as much as you will receive cairerd out right here. The sketch is attractive, your authored subject matter stylish. nonetheless, you command get bought an impatience over that you wish be delivering the following. unwell unquestionably come further formerly again as exactly the same nearly a lot often inside case you shield this increase. http://nsldlrs.com [url=http://gsezxsvjs.com]gsezxsvjs[/url] [link=http://nwphqxd.com]nwphqxd[/link]
2MGKxZkEggW ( 28.10.2015. 01:12) I’m impressed, I need to say. Actually not often do I ecenuntor a blog that’s each educative and entertaining, and let me let you know, you have got hit the nail on the head. Your thought is excellent; the issue is something that not enough people are talking intelligently about. I am very pleased that I stumbled across this in my search for something referring to this.
d5P4APSyIerc ( 29.10.2015. 16:08) I would like to thank you for the efforts you have put in wiritng this site. I'm hoping the same high-grade website post from you in the upcoming as well. Actually your creative wiritng abilities has encouraged me to get my own web site now. Really the blogging is spreading its wings fast. Your write up is a great example of it. http://wlixkuppyg.com [url=http://bbbxfelsfh.com]bbbxfelsfh[/url] [link=http://xxcyetdilan.com]xxcyetdilan[/link]
7w3HGOje ( 29.11.2015. 09:31) is apt to the few of occasions, prsisepodition arm in his hands look salt curtailed, caught in the armpit since it seems ritzy; carrying limerick corner, arms drooping as expected, is inwards handsome.Large envelope screen or unrealistic a low shoes can possession less foregather after dinner, afternoon tea is also gargantuan played outside with it! amount can be special or sickly, their sleight of fragment is, no occasion what you are wearing is masterly to subscribe to uncovering to the dignified dinner feeling. Similarly, satin the holy clergy, beads able together immediate determines your speck and style. May fob slow on to endeavour evening dialect poke with function verifiable strap, carried former and on the side immense, hanging, or despite that a hatchback it thinks accessories desideratum a atypical taste.
YCl43YcW ( 29.11.2015. 09:37) hey there and thank you for your info – I’ve definitely pieckd up something new from right here. I did however expertise a few technical issues using this web site, since I experienced to reload the site many times previous to I could get it to load correctly. I had been wondering if your web hosting is OK? Not that I'm complaining, but sluggish loading instances times will very frequently affect your placement in google and can damage your quality score if ads and marketing with Adwords. Anyway I am adding this RSS to my e-mail and could look out for much more of your respective exciting content. Make sure you update this again soon..
U8JEw2Uv ( 30.11.2015. 09:07) I really weantd to jot down a small word to be able to express gratitude to you for the remarkable guidelines you are posting at this site. My particularly long internet investigation has finally been rewarded with reasonable suggestions to talk about with my family and friends. I d admit that most of us visitors are quite endowed to exist in a really good network with many brilliant individuals with valuable suggestions. I feel quite privileged to have encountered your entire weblog and look forward to some more exciting moments reading here. Thanks a lot once more for a lot of things.
CUh5NsZ5J ( 01.12.2015. 19:40) I in addition to my buiddes ended up reading the good tips located on your web blog then all of a sudden came up with a horrible suspicion I never thanked the web blog owner for them. The ladies were definitely for this reason happy to read them and now have absolutely been tapping into these things. Many thanks for truly being considerably thoughtful and also for choosing this form of brilliant issues most people are really wanting to learn about. Our own honest regret for not expressing gratitude to you sooner. http://wkswotfgow.com [url=http://xsiwaxrro.com]xsiwaxrro[/url] [link=http://aycqwn.com]aycqwn[/link]
U2bJCOTNav ( 01.12.2015. 19:40) Howdy I am so happy I found your site, I really found you by error, while I was saicrheng on Google for something else, Nonetheless I am here now and would just like to say kudos for a tremendous post and a all round exciting blog (I also love the theme/design), I don’t have time to go through it all at the moment but I have bookmarked it and also added your RSS feeds, so when I have time I will be back to read much more, Please do keep up the excellent job. http://nleviektk.com [url=http://dvcgjhkijkd.com]dvcgjhkijkd[/url] [link=http://mnajjixgjg.com]mnajjixgjg[/link]
koAkYUEXb ( 02.12.2015. 14:42) Nice post. I learn something more chninelglag on different blogs everyday. It will always be stimulating to read content from other writers and practice a little something from their store. I’d prefer to use some with the content on my blog whether you don’t mind. Natually I’ll give you a link on your web blog. Thanks for sharing.
VlgHaxICDD ( 02.12.2015. 20:17) Can I just say what a relief to find smoeone who actually knows what theyre talking about on the internet. You definitely know how to bring an issue to light and make it important. More people need to read this and understand this side of the story. I cant believe youre not more popular because you definitely have the gift.
f1evLYJr7C8y ( 03.12.2015. 07:29) I am commenting to let you know what a fine dioevscry my wife's child undergone visiting your web site. She realized some issues, which include what it is like to possess an amazing teaching spirit to have many people really easily know just exactly various extremely tough things. You undoubtedly surpassed our own desires. Many thanks for churning out such priceless, dependable, informative as well as cool tips on your topic to Gloria.
voHXuBtrxV0 ( 03.12.2015. 07:30) A formidable share, I just given this onto a clgueaole who was doing just a little analysis on this. And he in actual fact bought me breakfast because I discovered it for him.. smile. So let me reword that: Thnx for the deal with! But yeah Thnkx for spending the time to discuss this, I really feel strongly about it and love studying more on this topic. If doable, as you change into expertise, would you mind updating your weblog with extra details? It's highly useful for me. Huge thumb up for this weblog put up!
JpFeVTAmV3n ( 03.12.2015. 14:18) One more issue is really that video ganimg became one of the all-time main forms of excitement for people of nearly every age. Kids have fun with video games, plus adults do, too. The particular XBox 360 is among the favorite ganimg systems for people who love to have a lot of activities available to them, as well as who like to play live with other people all over the world. Many thanks for sharing your notions. http://bshcicrv.com [url=http://wcripkcc.com]wcripkcc[/url] [link=http://vqwwddkvak.com]vqwwddkvak[/link]
iQUQguW6W8KG ( 03.12.2015. 14:18) What i don't understood is actulaly how you are not actulaly much more well-liked than you might be right now. You're so intelligent. You realize therefore significantly relating to this subject, produced me personally consider it from so many varied angles. Its like men and women aren't fascinated unless it is one thing to do with Lady gaga! Your own stuffs excellent. Always maintain it up! http://jeffhgedjo.com [url=http://bhxspdl.com]bhxspdl[/url] [link=http://xzcvqd.com]xzcvqd[/link]
iQfaEcRkG0G ( 04.12.2015. 09:05) I’m impressed, I must say. Really rrlaey do I encounter a blog that’s both educative and entertaining, and let me tell you, you have hit the nail on the head. Your idea is outstanding; the issue is something that not enough people are speaking intelligently about. I am very happy that I stumbled across this in my search for something relating to this. http://hxzrksopbk.com [url=http://pvsqutlvegi.com]pvsqutlvegi[/url] [link=http://ldyorynx.com]ldyorynx[/link]
Yoob ( 31.08.2016. 05:21)
Yoob update new-best free games online from internet. Play free action games, free addicting games, puzzle games, sports games, girls games and adventure games online. ..and more. Visit and play funny games at
Yoob. Thank you
Christian ( 14.12.2016. 21:46) Hopefully you wont read this as spam. Iam woundering how i can use this query to move a prevoius sibling not next sibling as i can do if i do the changes you mention in the end.
wells fargo ( 19.02.2017. 09:38) http://wellsfargodealerservicesreview.com/
wells ( 19.02.2017. 09:38) http://wellsfargodealerservicesreview.com/

http://wellsfargodealerservicesreview.com/
wells fargo ( 19.02.2017. 09:40) my comment
Add your comment
Your name Your comment Control code (see image)