MySql 一次更新两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8765490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql update two tables at once
提问by user962449
I have two tables that need need the exact same values for denormalization purposes.
我有两个表需要完全相同的值用于非规范化目的。
Here's the query.
这是查询。
first table
第一张桌子
UPDATE Table_One
SET win = win+1, streak = streak+1, score = score+200
WHERE userid = 1 AND lid = 1 LIMIT 1
second table
第二张桌子
UPDATE Table_Two
SET win = win+1, streak = streak+1, score = score+200
WHERE userid = 1 LIMIT 1
As you can see the only difference between both tables is their name and table two doesn't have the field lid
正如您所看到的，两个表之间的唯一区别是它们的名称，而第二个表没有该字段 lid
Anyway to combine both updates to just one?
无论如何要将两个更新合并为一个？
回答by rrehbein
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
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
Note: Multi-table doesn't support LIMIT, so this could cause more grief depending on the details.
注意：多表不支持 LIMIT，所以这可能会导致更多的悲伤，具体取决于细节。
Stored procedures or transactions may be a nicer solution.
存储过程或事务可能是更好的解决方案。
回答by Atonewell
If there is a one to one or one to many relation from Table_One to Table_Two, this would work:
如果从 Table_One 到 Table_Two 存在一对一或一对多关系，这将起作用：
UPDATE Table_One T1, Table_Two T2
SET T1.win = T1.win+1, T1.streak = T1.streak+1, T1.score = T1.score+200,
T2.win = T2.win+1, T2.streak = T2.streak+1, T2.score = T2.score+200
WHERE T1.userid = 1 AND T1.lid = 1 AND T2.userid = T1.userid;
回答by James Bradbury
If you can join the tables, then you could create a view of two tables, then update via that view. In your example it looks like useridmight be a suitable key.
如果您可以加入表，那么您可以创建两个表的视图，然后通过该视图进行更新。在您的示例中，看起来userid可能是合适的键。
In creating the view, you'd need to stick to the following guidelines.
在创建视图时，您需要遵守以下准则。
回答by Thomas Edwards
They're two separate queries and so must be treated as such. Sorry to say it, but if you're updating two tables with identical data, there's probably a better way to design your database. Remember to keep your programming DRY.
它们是两个独立的查询，因此必须如此对待。很抱歉这么说，但是如果您要更新具有相同数据的两个表，那么设计数据库可能有更好的方法。请记住保持您的编程DRY。
Edit: Should retract that; you can use it for multiple tables, but you can't use ORDER BYor LIMIT.
编辑：应该收回；您可以将它用于多个表，但不能使用ORDER BY或LIMIT。
