Syntax error in Access SQL query inner join

Question

I have written the following query in MS Access SQL:

UPDATE [B-K Data]
SET [Supplier 1] = [PNTestImport].[MFR], [Supplier 1 order no] = [PNTestImport].[MFR_PN]
FROM [PNTestImport]
INNER JOIN [BK-Analogic-PN-CDB2006-import] on [B-K Data].[B-K no] = [BK-Analogic-PN-CDB2006-import].[BK PN]
INNER JOIN  [PNTestImport] on  [PNTestImport].[ITEM_NUMBER] = [BK-Analogic-PN-CDB2006-import].[Analogic PN];

I get a syntax error when I try to run the query, but no identification of where the problem lies.

When Googling the error, I found other with similar problem but no answer that works. What am I doing wrong here?

using space in database object name is NOT recommend, although there is no actual harm if you remember always include [ brackets ] ( this is already a point not to do this), but imagine you accidentally type double space and spend hours just to find out where the typo is. — T. Peter
– T. Peter, Commented Feb 19, 2021 at 8:37
Turn on all compiler warnings. For compiler errors chop code until OK & add back. Runtime find & show input & output up to the 1st point that doesn't have the program in the state you expect. Read the grammar & manual. Show that constituent subexpressions are OK. (Debugging fundamentals.) For code questions give a minimal reproducible example. How to Ask help center Before considering posting please read the manual & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. — philipxy
– philipxy, Commented Feb 19, 2021 at 8:43

Erik A · Accepted Answer · 2021-02-19 08:42:59Z

1

There's no FROM in an Access UPDATE clause. Tables are specified directly after UPDATE:

UPDATE [B-K Data]
INNER JOIN [BK-Analogic-PN-CDB2006-import] on [B-K Data].[B-K no] = [BK-Analogic-PN-CDB2006-import].[BK PN]
INNER JOIN  [PNTestImport] on  [PNTestImport].[ITEM_NUMBER] = [BK-Analogic-PN-CDB2006-import].[Analogic PN]
SET [Supplier 1] = [PNTestImport].[MFR], [Supplier 1 order no] = [PNTestImport].[MFR_PN]

Note that this also means all tables are editable in a single UPDATE clause, and all tables must be writable. There also cannot be ambiguity through joins, which can lead to problems with many-many matches.

answered Feb 19, 2021 at 8:42

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2021-02-19 08:32:30Z

0

In ms access you need () parentheses ro the form clause with join around the

UPDATE [B-K Data]
SET [Supplier 1] = [PNTestImport].[MFR], [Supplier 1 order no] = [PNTestImport].[MFR_PN]
FROM (( [PNTestImport]
INNER JOIN [BK-Analogic-PN-CDB2006-import] on 
       [B-K Data].[B-K no] = [BK-Analogic-PN-CDB2006-import].[BK PN])
INNER JOIN  [PNTestImport] on 
       [PNTestImport].[ITEM_NUMBER] = [BK-Analogic-PN-CDB2006-import].[Analogic PN]);

answered Feb 19, 2021 at 8:32

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Collectives™ on Stack Overflow

Syntax error in Access SQL query inner join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related