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

Question

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

your set clause is funny there should only be one = Also did you run a select with join to see if there are any row that match — nbk, Mar 29 '22 at 20:44
@nbk I deleted the beginning of it, as I thought it was weird too, both "tbl_MTO_vs_ETO.ProductLine = [tbl_MTO_vs_ETO].[ProductLine]=[tbl_line_items].[Product_Line]" & "[tbl_MTO_vs_ETO].[ProductLine]=[tbl_line_items].[Product_Line]" do the same thing for me. ;; No, I didn't try select with a join yet. I'll come back with an update. — Marcus, Mar 29 '22 at 20:47
@nbk the select and join is able to find the related order and line numbers, but isn't pulling the product line item. — Marcus, Mar 29 '22 at 20:56
you have three conditions that must be met so that the row gets updated. access doesn't find any match for that three conditions, that is why i asks if you run the select with these thre on clauses — nbk, Mar 29 '22 at 21:02
there only 2 on conditions in the select, in your update you have 3 — nbk, Mar 29 '22 at 21:08
@nbk Ah! I got it!! I didn't know the "ON" were conditions. I took away the original product line condition from the update query and its working now. My table inserted the values. — Marcus, Mar 29 '22 at 21:11
@nbk Would you mind putting your comment in the answer section so I may accept it? "UPDATE query contained too many conditions" — Marcus, Mar 29 '22 at 21:16

score 1 · Accepted Answer · answered Mar 29 '22 at 21:21

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

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

1 Answers1