CODEWITHSUNDEEP
sqlsyntax-errorinner-joinms-access-2010
Ioragi
Ioragi

Reputation: 115

Syntax error in Access SQL query inner join

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?

Upvotes: 0

Views: 54

Answers (2)

Erik A
Erik A

Reputation: 32642

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.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

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

Upvotes: 0

Related Questions