Sure looks like you're updating positions in a hierarchial collection of help pages using nested sets, though the algorithm is a touch different that what I've always used, and I can't say if it's a bug or if it's just different. However, I can tell you from experience that two sequential queries that use the WHERE clause to limit what gets updated is faster unless the network latency between your app server and DB server is huge (and so doing one less round trip makes up for the difference in query execution time).
cheers, barneyb On 12/22/06, Josh Nathanson <[EMAIL PROTECTED]> wrote: > Barney, thanks -- that got me going in the right direction, although it > wouldn't let me use an expression as the result of the conditional, I think > because one of the values in the expression was the value of a column -- > however I was able to tweak it so my result could be a simple integer. > Below is what worked. Super bonus points if anyone can guess why I'm doing > this. > > <cfquery datasource="#Request.ds#"> > UPDATE helpPages > SET > rgt = rgt - > CASE WHEN rgt > #src_rgt# AND lft > #src_lft# > THEN 2 > WHEN (rgt < #src_rgt# AND lft > #src_lft#) > OR (rgt > #src_rgt# AND lft < #src_lft#) > THEN 1 > ELSE 0 > END > ,lft = lft - > CASE WHEN rgt > #src_rgt# AND lft > #src_lft# > THEN 2 > WHEN rgt < #src_rgt# AND lft > #src_lft# > THEN 1 > ELSE 0 > END > WHERE 1 = 1 > </cfquery> > > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 100 invites. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264951 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
