How can I join three separated UPDATE statements?

Question

I have three separated queries. Can I mix them and create one query instead of these three queries?

Query1:

UPDATE users SET reputation = reputation + 15, 
                 money = money + (SELECT SUM(responder_amount) 
                                    FROM money WHERE post_id = :post_id AND paid IS NULL)
 WHERE id = :Responder

Query2:

UPDATE users SET reputation = reputation + 2,
                 money = money + (SELECT SUM(op_amount) 
                                    FROM money WHERE post_id = :post_id AND paid IS NULL)
 WHERE id = :OP

Query3:

UPDATE money SET paid = 1 WHERE post_id = :post_id

Can I make one query made up of those three queries?

No you cannot update multiple tables in a single sql statement. You might be able to rewrite those first 2 queries to be a single statement, but I doubt it would be any more efficient. — sgeddes, May 24 '16 at 23:18
@sgeddes Ah I see .. However I read somewhere updating multiple tables would be possible by `JOIN`. I don't know how, I just heard that. — stack, May 24 '16 at 23:20
@sgeddes this is not true, mysql supports a multiple table update syntax. However, I'm not sure if you can use that here because of the sum() function. These statements should be part of a transaction. — Shadow, May 24 '16 at 23:23
@Shadow, thanks -- I didn't look at the database tagged. You are absolutely correct: http://stackoverflow.com/a/9417254/1073631 — sgeddes, May 24 '16 at 23:25
Based on the available information it is impossible to tell if you can combine the 3 statements into one. The use of the sum() function indicates that there may be multiple records in the money table with the same post_id. The relationship between the money and users tables is not known either. Even if the 3 statements could be combined into one, I'm not sure if I spent any time working that out. I would simply encapsulate the 3 statements into a single transaction. This way all 3 statements will either succeed or fail together. — Shadow, May 24 '16 at 23:33
@Shadow Ok fine, How about just those first two queries can get combine? — stack, May 24 '16 at 23:37
The same applies: I do not understand how the 2 queries relate to each other and again, I would rather keep things simple and use a transaction. Why do you want to combine the queries into one this badly? A single complex query may not be more efficient than 3 separate ones. — Shadow, May 24 '16 at 23:58
@Shadow Ok fine. I will go with three separated queries. Just which kind of transaction do you mean? PDO transaction or MySQL transaction? Or it doesn't matter? — stack, May 25 '16 at 07:51

score -2 · Answer 1 · answered May 24 '16 at 23:38

if you are updateing the same column, you can use case but in your examples,
first one and second one can be written in 1 update statement, but in the third one you are updating different column, ona a different table so as far as i know, i cannot be done.

How can I join three separated UPDATE statements?

1 Answers1