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.