组合sql查询.从一个表中减去，再加到另一个表中

Question

我需要区分玩家的金钱，将武器添加到礼物列表中。我有一个这样的代码:

string itemsQueryUpdate = @"update gunitems set amount = amount - 1 where id=@id";
using (MySqlCommand itemsUpdateCommand = new MySqlCommand(itemsQueryUpdate, mycon))
{
itemsUpdateCommand.Parameters.AddWithValue("@id", gunItem.Id);
itemsUpdateCommand.ExecuteNonQuery();
}
myReader.Close();

string usersQueryUpdate = @"update users set coins = coins - 1 where id=@id";
using (MySqlCommand usersUpdateCommand = new MySqlCommand(usersQueryUpdate, mycon))
{
usersUpdateCommand.Parameters.AddWithValue("@id", user.Id);
usersUpdateCommand.ExecuteNonQuery();
}
myReader.Close();

string giftQuery = @"insert into gifts (user_id,gun_item_id,count,steam_link,order_date) values (@user_id,@gun_item_id,@count,@link,@order_date);";
using (MySqlCommand giftCommand = new MySqlCommand(giftQuery, mycon))
{
giftCommand.Parameters.AddWithValue("@user_id", user.Id);
giftCommand.Parameters.AddWithValue("@gun_item_id", gunItem.Id);
giftCommand.Parameters.AddWithValue("@count", 1);
giftCommand.Parameters.AddWithValue("@link", "link");
DateTime theDate = DateTime.Now;
giftCommand.Parameters.AddWithValue("@order_date", theDate.ToString("yyyy-MM-dd H:mm:ss"));
giftCommand.ExecuteNonQuery();
}
myReader.Close();

我如何优化这个?

Answer 1

整个操作应该使用一个查询。像

UPDATE user                                  -- users table
JOIN usergun ON user.id = usergun.userid   -- user's gun table
JOIN gunitem ON usergun.gunid = gunitem.id -- guns table
SET user.coins = user.coins - 1,             -- substract the cost
usergun.amount = usergun.amount + 1,     -- add an item
gunitems.amount = gunitems.amount - 1    -- remove item from the store
WHERE id = user.id = @userid         -- specify user
AND gunitem.id = @gunid            -- specify gun
AND user.coins >= 1                -- ensure that the user have money
AND gunitems.amount >= 1           -- ensure that the gun is present in the store
-- AND ...      -- another conditions