CODEWITHSUNDEEP
sqlms-accessjoinsql-update
Kangze Huang
Kangze Huang

Reputation: 351

How to UPDATE a table with NESTED JOIN. I am getting syntax error in MS Access

I am trying to UPDATE a table in MS Access but the table I cannot figure out what my Syntax Error is! MS Access throws "Syntax Error in Join expression" and highlights my SET command. Can anyone help spot my mistake?

UPDATE tbl_Items
INNER JOIN 

(SELECT *
FROM tbl_Inventory
INNER JOIN tbl_Inputs
ON tbl_Inventory.class = tbl_Inputs.class)

ON tbl_Items.PRODUCT_ID = tbl_Inputs.PRODUCT_ID

SET tbl_Items.status = tbl_Inventory.status

WHERE tbl_Items.status <> tbl_Inventory.status

As you can see, I want to update tbl_Items to have a status matching its corresponding record in tbl_Inventory. The query should only be applied to rows were tbl_Items and tbl_Inventory disagree. Before I can access tbl_Inventory, I need to join with tbl_Inputs.

Upvotes: 0

Views: 66

Answers (2)

Kangze Huang
Kangze Huang

Reputation: 351

I found the solution, which was to use AS to create a nickname. The below query works:

UPDATE tbl_Items
INNER JOIN 

(SELECT *
FROM tbl_Inventory
INNER JOIN tbl_Inputs
ON tbl_Inventory.class = tbl_Inputs.class) AS nickname

ON tbl_Items.PRODUCT_ID = nickname.PRODUCT_ID

SET tbl_Items.status = nickname.status

WHERE tbl_Items.status <> nickname.status

Upvotes: 1

Ross Presser
Ross Presser

Reputation: 6255

I'm not 100% sure of Access syntax for this. However, in standard SQL, your statement should be written thus:

UPDATE  tbl_items
SET     tbl_items.status = tbl_Inventory.status
FROM    tbl_items
INNER JOIN tbl_Inputs
        ON tbl_items.PRODUCT_ID = tbl_Inputs.PRODUCT_ID
INNER JOIN tbl_Inventory
        ON tbl_Inventory.class = tbl_Inputs.class
WHERE   tbl_items.status <> tbl_Inventory.status;

Furthermore, the tbl_ prefix is unnecessary and bad practice. If @Celko were here he'd undoubtedly insist you remove it.

Upvotes: 0

Related Questions