尝试从一个表中获取列值，并使用 UPDATE & JOIN 将其更新到另一个表中

Question

MS Access，使用SQL，尝试遵循这篇文章——

我的目标是使用tbl_line_items.Order_Number和tbl_line_items.Line_Number作为主键，取列值tbl_line.items_Product_Line，并将其更新为tbl_MTO_vs_ETO.ProductLine。

基本上，我想得到产品线和它各自的线&订单号，并将其插入MTO与ETO表中。

当前代码：

UPDATE tbl_line_items INNER JOIN tbl_MTO_vs_ETO ON (tbl_line_items.Order_Number = tbl_MTO_vs_ETO.Order) AND (tbl_line_items.Line_Number = tbl_MTO_vs_ETO.Line) AND (tbl_line_items.Product_Line = tbl_MTO_vs_ETO.ProductLine) 
SET tbl_MTO_vs_ETO.ProductLine = [tbl_MTO_vs_ETO].[ProductLine]=[tbl_line_items].[Product_Line];

我的查询会追加，但不返回任何内容(">您将要更新0行"(。我认为我应该使用UPDATE语句，因为记录已经存在，我最近刚刚添加了产品线列以进行进一步筛选。

对于nbk——

我运行了一个SELECT和JOIN查询，能够成功地提取碎片订单和行号，并找到每个记录的产品线。现在的问题是，如何将其转换为UPDATE查询？

代码：

SELECT tbl_line_items.Product_Line, tbl_line_items.Order_Number, tbl_line_items.Line_Number
FROM tbl_MTO_vs_ETO INNER JOIN tbl_line_items ON (tbl_MTO_vs_ETO.line = tbl_line_items.Line_Number) AND (tbl_MTO_vs_ETO.Order = tbl_line_items.Order_Number);

Answer 1

您的SELECT有两个条件

SELECT 
tbl_line_items.Product_Line,
tbl_line_items.Order_Number,
tbl_line_items.Line_Number
FROM
tbl_MTO_vs_ETO
INNER JOIN
tbl_line_items ON (tbl_MTO_vs_ETO.line = tbl_line_items.Line_Number)
AND (tbl_MTO_vs_ETO.Order = tbl_line_items.Order_Number);

但是你的UPDATE有三个

UPDATE tbl_line_items 
INNER JOIN tbl_MTO_vs_ETO 
ON (tbl_line_items.Order_Number = tbl_MTO_vs_ETO.Order) AND (tbl_line_items.Line_Number = tbl_MTO_vs_ETO.Line) AND (tbl_line_items.Product_Line = tbl_MTO_vs_ETO.ProductLine) 
SET tbl_MTO_vs_ETO.ProductLine = [tbl_MTO_vs_ETO].[ProductLine]=[tbl_line_items].[Product_Line];

为了使UPDATE发生，必须满足这三个条件

所以使用

UPDATE 
tbl_MTO_vs_ETO
INNER JOIN
tbl_line_items ON (tbl_MTO_vs_ETO.line = tbl_line_items.Line_Number)
AND (tbl_MTO_vs_ETO.Order = tbl_line_items.Order_Number);
SET tbl_MTO_vs_ETO.ProductLine = [tbl_MTO_vs_ETO].[ProductLine]=[tbl_line_items].[Product_Line];

使UPDATE在上工作