如何在一条语句中更新两个表? [英] How to update two tables in one statement?

问题描述

UPDATE table1, tmpList
  SET table1.ts = tmpList.ts_value
WHERE table1.id = tmpList.id

UPDATE table2, tmpList
  SET table2.ts = tmpList.ts_value
WHERE table2.id = tmpList.id

我正在使用MySQL

I'm using MySQL

推荐答案

假定每个id都出现在两个表中(理想情况下仅出现一次):

Assuming every id appears in both tables (ideally only once):

update tmpList inner join table1 using (id) inner join table2 using (id)
    set table1.ts = tmpList.ts_value, table2.ts=tmpList.ts_value;

更新:简单地使用左联接而不是内部联接，即使对于仅在一个表中的id来说，也可以完成这项工作-不适用的set子句似乎只是被跳过了；显然，我应该早点尝试一下.

Update: simply using left joins instead of inner joins makes this work even for ids that are only in one table - the inapplicable set clause seems to just be skipped; I should have tried it earlier, apparently.

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