There a lot of funny business going on in this SQL statement. I would try to accomplish the logic outside the SQL statement. I you need conditionals I would limited to <cfif> statements any thing more than that seems a little messy for lack of a better term.
---------- Original Message ---------------------------------- From: "Josh Nathanson" <[EMAIL PROTECTED]> Reply-To: [email protected] Date: Fri, 22 Dec 2006 13:57:21 -0800 > >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 > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:264948 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
