MySql一次更新两个表 [英] MySql update two tables at once

问题描述

我有两个表，它们需要完全相同的值以进行非规范化.

I have two tables that need need the exact same values for denormalization purposes.

这是查询.

第一张桌子

UPDATE Table_One 
SET win = win+1, streak = streak+1, score = score+200 
WHERE userid = 1 AND lid = 1 LIMIT 1

第二张桌子

UPDATE Table_Two 
SET win = win+1, streak = streak+1, score = score+200 
WHERE userid = 1 LIMIT 1

如您所见，两个表之间的唯一区别是它们的名称，并且两个表没有字段lid

As you can see the only difference between both tables is their name and table two doesn't have the field lid

是否将两个更新合并为一个?

Anyway to combine both updates to just one?

推荐答案

如文档中所述，应该可以进行多表更新.

It should be possible with a multi-table update, as described in the documentation.

http://dev.mysql.com/doc/refman/5.5/en/update .html

UPDATE Table_One a INNER JOIN Table_Two b ON (a.userid = b.userid)
SET
  a.win = a.win+1, a.streak = a.streak+1, a.score = a.score+200,
  b.win = b.win+1, b.streak = b.streak+1, b.score = b.score+200 
WHERE a.userid = 1 AND a.lid = 1 AND b.userid = 1

注意:多表不支持LIMIT，因此根据具体情况，这可能会引起更多麻烦.

Note: Multi-table doesn't support LIMIT, so this could cause more grief depending on the details.

存储过程或事务可能是更好的解决方案.

Stored procedures or transactions may be a nicer solution.

这篇关于MySql一次更新两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！