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

Question

MS，使用SQL，尝试遵循this post--

我的目标是使用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];

我的查询追加，但什么也不返回("You将要更新0行“)。我认为我应该使用UPDATE语句，因为记录已经存在，我最近添加了产品行列以进行进一步的筛选。

对于nbk--

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

代码：

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就能工作