CODEWITHSUNDEEP
mysqlsqlsql-update
stack
stack

Reputation: 10228

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?

Upvotes: 0

Views: 972

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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 user WHEN :op   THEN 2 
                                   WHEN :user THEN 15 
                         END),
        fee = fee + 
             (CASE user 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)

Upvotes: 2

Related Questions