MySql 一次更新两个表

2021-11-20 00:00:00 sql-update mysql

我有两个表需要完全相同的值用于非规范化目的.

I have two tables that 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

注意:Multi-table 不支持 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.

相关文章