stack stack - 5 months ago 12
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

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)
Comments