6 messages in com.mysql.lists.mysqlRe: Possible to use a conditional in ...| From | Sent On | Attachments |
|---|---|---|
| Scott Haneda | 27 Oct 2005 18:34 | |
| Scott Haneda | 27 Oct 2005 21:22 | |
| Jasper Bryant-Greene | 27 Oct 2005 21:56 | |
| Brent Baisley | 28 Oct 2005 05:52 | |
| Scott Haneda | 28 Oct 2005 15:09 | |
| Michael Stassen | 31 Oct 2005 09:02 |
| Subject: | Re: Possible to use a conditional in this UPDATE |
|---|---|
| From: | Michael Stassen (Mich...@verizon.net) |
| Date: | 10/31/2005 09:02:52 AM |
| List: | com.mysql.lists.mysql |
Scott Haneda wrote:
on 10/28/05 5:52 AM, Brent Baisley at bre...@landover.com 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