From: Date: October 31 2005 6:03pm
Subject: Re: Possible to use a conditional in this UPDATE
List-Archive: http://lists.mysql.com/mysql/191067
Message-Id: <43664E52.7020402@verizon.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Scott Haneda wrote:
> on 10/28/05 5:52 AM, Brent Baisley at brent@stripped wrote:
> 
> 
>>You can nest the IF statement, putting another where 'soon' is like
>>Jasper suggested. Or you can use the CASE WHEN THEN construct if you
>>have a lot of conditions you need to check for.
> 
> Can you show me an example of the CASE method, I tried and it would error,
> the docs are a wee bit confusing in that regards.

Hard to be sure without seeing what you tried, but I'd guess you expected CASE 
to be a logical flow-control operator (as the manual implies) instead of a 
function.  I also notice that the manual is currently screwed up, running the 
two syntaxes together.

So far, you have this:

   UPDATE cart, products
   SET cart.ship_status = IF(products.ship_status = 1, 'now', 'soon')
   WHERE products.id = cart.product_id
     AND cart.session_id = "5511";

The problem is that 'soon' is not the only answer for products.ship_status != 1. 
  You haven't told us how to determine the other values, however.  Are they all 
based on products.ship_status?  If so, you should be able to do something like:

   UPDATE cart, products
   SET cart.ship_status = CASE products.ship_status
                              WHEN 1 THEN 'now'
                              WHEN 2 THEN 'soon'
                              WHEN 3 THEN 'next week'
                              WHEN 4 THEN 'next month'
                              ELSE 'never'
                          END
   WHERE products.id = cart.product_id
     AND cart.session_id = "5511";

Michael