CODEWITHSUNDEEP
mysql
haosmark
haosmark

Reputation: 1127

Conditional isn't working

Spent an hour already searching and trying to get this work. Starting to hate mysql. What am I doing wrong here?

UPDATE product_reward pr
  LEFT JOIN product p ON (pr.product_id = p.product_id)
  LEFT JOIN product_special ps ON (pr.product_id = ps.product_id)

SELECT CASE ps.price  
  WHEN NULL
    THEN SET pr.points = ROUND(p.price * 1);
  ELSE 
    SET pr.points = ROUND(ps.price * 1);
END;

I tried using normal IF ELSE, but apparently that only works inside of procedures. I'm trying to set points based on normal price only if special price is NULL.

Upvotes: 0

Views: 44

Answers (1)

MatthiasLaug
MatthiasLaug

Reputation: 2914

try this or have a look into Mysql Update with if statement

UPDATE product_reward pr
  LEFT JOIN product p ON (pr.product_id = p.product_id)
  LEFT JOIN product_special ps ON (pr.product_id = ps.product_id)

SET pr.points = ROUND(COALESCE(ps.price, p.price) * 1)

Upvotes: 2

Related Questions