IF. CASE. Mysql

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";

Bottom line, if you need some compact SQL like example above, you can combine it with other SQL operator such as DATE_ADD, etc. Let’s try

UPDATE user
SET expiry_date = IF(expiry_date < CURDATE(), CURDATE(), NULL)
WHERE user_id =5

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s