stack stack - 1 year ago 85
MySQL Question

How to update multiple columns based on condition?

I have this query:

UPDATE user
SET reputation = reputation +
(CASE id WHEN :op THEN 2
WHEN :user THEN 15
END)
WHERE user in (:user, :op)


And I also want to update one more column based on those conditions of that
CASE()
function. Currently I do that by another query:

UPDATE user
SET fee = fee +
(CASE id WHEN :op THEN (SELECT SUM(op_val) FROM money WHERE id = :post_id)
WHEN :user THEN (SELECT SUM(user_val) FROM money WHERE id = :post_id)
END)
WHERE user in (:user, :op)


Well as you see the conditions of those two queries above are identical. How can I mix them and make a single query instead?

Answer Source

Your first query looks strange. Why would use mix comparisons of user and id to the input variables. Do you intend this?

UPDATE user
   SET reputation = reputation + 
                    (CASE id WHEN :op   THEN 2 
                             WHEN :user THEN 15 
                     END)
WHERE id in (:user, :op);

A set clause can set multiple columns:

UPDATE user
    SET reputation = reputation + 
                        (CASE id WHEN :op   THEN 2 
                                 WHEN :user THEN 15 
                         END),
        fee = fee + 
             (CASE id WHEN :op   THEN (SELECT SUM(op_val) FROM money WHERE id = :post_id)
                      WHEN :user THEN (SELECT SUM(user_val) FROM money WHERE id = :post_id)
              END)
WHERE user in (:user, :op);

You can also write the second condition as:

        fee = fee + 
             (SELECT SUM(CASE id WHEN :op THEN op_val ELSE user_val END) FROM money WHERE id = :post_id)