在mysql中的单个查询中更新两个表 [英] update two tables in single query in mysql

问题描述

我有两个名为supply和equipment的表.这两个表有一个公共字段，名为:IAR_NO

I have two tables named supply and equipment. These two tables have a common field named: IAR_NO

现在，我要使用一个 query 更新所有两个表的所有记录，这些记录的IAR_NO中都为0.

Now, what I want is to update using a single query, all the records from both tables which have 0 in its IAR_NO.

什么是对此的最佳查询?

what could be the best query for this?

我当前正在使用，但无法正常工作

I am currently using but does not work

update supply, equipment 
set supply.IAR_NO =  "9", equipment.IAR_NO= "9 " 
where equipment.IAR_NO = 0 and supply.IAR_NO = 0

推荐答案

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

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 supply a INNER JOIN equipment b ON (a.IAR_NO= b.IAR_NO)
SET a.IAR_NO =  "9" , b.IAR_NO =  "9"
WHERE equipment.IAR_NO = 0 and supply.IAR_NO = 0;

注意:

如果您要更新两个具有相同数据的表，则可能是一种更好的数据库设计方法.记住要保持编程 DRY .

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.

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