MySQL transaction to single query

MySQL transaction to single query


I have a game with 2 users, at the game start I want to save their initial
balance then cut some amount of money from them.
Currently I do this with 3 queries in a transaction. Here's the code:
#region cut_money
bool success = true;
tr = conn.BeginTransaction();
cmd = new MySqlCommand("UPDATE games SET user1_starting_balance = (SELECT
money FROM users WHERE users.id = games.user1_id), user2_starting_balance
= (SELECT money FROM users WHERE users.id = games.user2_id) WHERE id =
@id", conn, tr);
cmd.Parameters.AddWithValue("id", this.id);
if (cmd.ExecuteNonQuery() != 1)
success = false;
for (int i = 0; i <= 1; i++)
lock (this.users[i].id.ToString())
{
cmd = new MySqlCommand("UPDATE users SET money = money - @bmoney,
blocked_money = blocked_money + @bmoney WHERE id = @user_id AND
money >= @bmoney", conn, tr);
cmd.Parameters.AddWithValue("bmoney", money_to_cut);
cmd.Parameters.AddWithValue("user_id", this.users[i].id);
if (cmd.ExecuteNonQuery() != 1)
{
success = false;
break;
}
}
if (success)
tr.Commit();
else
tr.Rollback();
conn.Close();