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:264940 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
