mysql - update two records at once

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

First I do a SELECT to check that $pa开发者_Python百科y_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?

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.

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

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.