MySQL - Combining 2 queries that have different SET and WHERE clauses

Question

A while ago @Richard Harris gave a great answer for a similar question, but I believe my situation is slightly different.

As you can see I have 2 consecutive UPDATE statements targeting the same table and fields but with different SET and WHERE clauses.

$this->db->query("
UPDATE user_profiles
SET    reputation = reputation + 15
WHERE  user_id = $answer_author_id;
");

$this->db->query("
UPDATE user_profiles
SET    reputation = reputation + 2
WHERE  user_id = $user_id;
");

I wonder if this can be combined into a single query, or if this requires further normalization. Also, are these consecutive queries too inefficient? If not, I wouldn't bother trying to combine into a single query.

Your thoughts on this are much appreciated.

score 2 · Accepted Answer · answered Jul 31 '11 at 02:51

2

you can do this:

UPDATE user_profiles
SET    reputation = reputation + 
           case when user_id = $answer_author_id then 15
                when user_id = $user_id then 2
                else 0
           end
WHERE  user_id = $answer_author_id or user_id = $user_id;

answered Jul 31 '11 at 02:51

Derek

21,828
7
53
61

thanks again @derek ;) -- any idea re efficiency of this solution vs. 2 queries? just curious in case you've ever benchmarked this kind of situation – pepe Jul 31 '11 at 02:56
just did a quick benchmark check (averaged 5 server responses for your code vs. '2 queries') using my DB software via SSH -- results: your code (ave. 64.1 ms) -- 2 queries (ave. 162.6 ms) -- pretty impressive no? – pepe Jul 31 '11 at 03:31

score 1 · Answer 2 · edited May 23 '17 at 11:55

1

The solution actually isn't terribly different from this one, just a tad more nested.

UPDATE my_table SET reputation = 
    IF( USER_ID = $user_id, REPUTATION + 2, REPUTATION + 15 ) 
        WHERE USER_ID IN ( $user_id, $answer_author_id );

edited May 23 '17 at 11:55

Community

1
1

answered Jul 31 '11 at 02:49

cwallenpoole

79,954
26
128
166

thx - i think i'll try derek's code for the meanwhile since it's a bit more readable (for maintenance sake) – pepe Jul 31 '11 at 03:04
Suit yourself, they boil down to the same query either way. – cwallenpoole Jul 31 '11 at 03:09

score 1 · Answer 3 · answered Jul 31 '11 at 02:50

UPDATE user_profiles SET reputation = reputation + IF(user_id=$answer_author_id,15,2) WHERE user_id = $answer_author_id OR user_id = $answer_author_id;

This works, but I recommend using 2 queries simply for code readability, unless efficiency is super important.

MySQL - Combining 2 queries that have different SET and WHERE clauses

3 Answers3