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> ----- Original Message ----- From: "Barney Boisvert" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Friday, December 22, 2006 2:21 PM Subject: Re: CASE statement in MySQL > The CASE construct is an expression. The structure of the SQL > statement cannot be changed with a CASE construct, all you can do is > dynamically compute a value. To put that nother way, you can only use > a CASE where you can use a value/expression. > > Instead of this (what you have): > UPDATE myTable > SET > CASE WHEN cond1 > THEN col1 = col1 - 1, col2 = col2 - 1 > WHEN cond2 > THEN col1 = col1 - 2, col2 = col2 - 2 > END > > you need to do this: > UPDATE myTable > SET > col1 = CASE WHEN cond1 THEN col1 - 1 WHEN cond2 THEN col1 - 2 END, > col2 = CASE WHEN cond2 THEN col2 - 1 WHEN cond2 THEN col2 - 2 END > > cheers, > barneyb > > On 12/22/06, Josh Nathanson <[EMAIL PROTECTED]> wrote: >> Hey SQL gurus, >> >> I am trying to use a CASE statement in a MySQL query to conditionally >> update >> a number of rows in a table. >> >> However, if I try to use anything in the "THEN" clause other than a >> string, >> it fails with "Error in SQL syntax." >> >> I am trying to set values in the table differently depending on a >> condition, >> like so (simplified): >> >> UPDATE myTable >> SET >> CASE WHEN cond1 >> THEN col1 = col1 - 1, col2 = col2 - 1 >> WHEN cond2 >> THEN col1 = col1 - 2, col2 = col2 - 2 >> etc. >> END >> >> I also tried putting the SET after the THEN and it also fails. >> >> Is there any way to make this happen all in one query, or am I going to >> have >> to go outside of SQL and do a CF loop? >> >> -- Josh >> > > -- > 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:264949 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
