mysql - update two records at once

Question

Using PHP's msqli is it possible to update two records with one query?

First I do a SELECT to check that $pay_user has enough game currency in his account, if he does then I do the following...

My update query is:

"UPDATE account SET money= money - ".$money." WHERE User_id=".$pay_user

"UPDATE account SET money= money + ".$money." WHERE User_id=".$recieve_user

It is transactional to stop dirty read's.

I was hoping to save on a query and call it as one... is it possible?

Answer 1

Since the two where clauses are not the same, you cannot combine these queries into one statement.

You can mark it as a TRANSACTION so they both execute at the same time.

Answer 2

I recommend using a stored procedure to do this. it will be one call from php, and if it fails in the middle the SP should issue a rollback.

Answer 3

refer to http://dev.mysql.com/doc/refman/5.0/en/update.html

code sample

update account a1, account a2 
set a1.money = a1.money - $money , a2.money = a2.money + $money
where a1.user_id = $pay_user and a2.user_id = $recv_user