Trying to take column value from one table and update it into another using UPDATE & JOIN-CodePudding

MS Access, with SQL, tried following this post--

My objective is to use tbl_line_items.Order_Number and tbl_line_items.Line_Number as the primary keys, take the column value tbl_line.items_Product_Line, and update it to the tbl_MTO_vs_ETO.ProductLine.

Basically, I want get the product line with its respective line & order number and inserting it into the MTO vs ETO table.

Current code:

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];

My query appends, but returns nothing ("You are about to update 0 row(s)"). I believe I should be using the UPDATE statements as the records already exist, I just recently added that product line column for further filtering.

For nbk--

I ran a SELECT and JOIN query, and was able to successfully pull the shard order and line numbers, and find the product lines for each record. The question is now, how to I translate it over to an UPDATE query?

Code:

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);

CodePudding user response：

Your SELECT has two condition

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);

but your UPDATE has three

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];

these three condition have to be met so that the UPDATE can happen

So use

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];

So that the UPDATE works