1

I have the following query that extracts unique records from a table:

SELECT First(t1.[Site Number]) AS [FirstOfSite Number], t1.[Customer Number], First(t1.Address1) AS FirstOfAddress1
FROM tblImport1 AS t1
GROUP BY t1.[Customer Number]
ORDER BY t1.[Customer Number];

I want to update a second table, say tblimport, according to the query above by linking the fields t1.[Site Number] withtblimport.[Site Number].

I made the following query:

UPDATE tblimport 
INNER JOIN tblimport on  [tblimport].[site number]=[t1].[site number] 
  (SELECT First(t1.[Site Number]) AS [FirstOfSite Number], t1.[Customer Number], First(t1.Address1) AS FirstOfAddress1
  FROM tblImport1 AS t1
  GROUP BY t1.[Customer Number])
set tblimport.address1=t1.address1

However I get syntax error. Where am I going wrong here?

EDIT

I read the article suggested and I have amended the query this way:

UPDATE D 
set D.address1=t1.address1
FROM tblimport as D
    INNER JOIN (SELECT First(t1.[Site Number]) AS [FirstOfSite Number], t1.[Customer Number], First(t1.Address1) AS FirstOfAddress1  FROM tblImport1 AS t1  GROUP BY t1.[Customer Number])
    ON t1.[Site Number]=D.[Site Number]

But I still have the syntax error.

Community
  • 1
  • 1
pio pio
  • 732
  • 1
  • 7
  • 29
  • take a look [here](http://stackoverflow.com/questions/12882212/sql-updating-from-an-inner-join) for the correct `UPDATE FROM` syntax. Your query is quite misformed: you inner join a table with iteself without giving each an alias. in the join condition you reference a table that only will be known later down the script. The sub select is just in the middle of nowhere, it should probably be placed where the second tblimport ist joined. – luk2302 Jun 29 '15 at 09:56
  • I have just amended the query accordingly but I still have the syntax error. – pio pio Jun 29 '15 at 10:35
  • Try giving your select statement in the Inner Join an alias, use the alias in your ON statement and call the correct field in your SET: INNER JOIN (SELECT First(t1.[Site Number]) AS [FirstOfSite Number], t1.[Customer Number], First(t1.Address1) AS FirstOfAddress1 FROM tblImport1 AS t1 GROUP BY t1.[Customer Number]) AS Q ON Q.[Site Number]=D.[Site Number] - and in your set use: SET D.Address1 = Q.FirstOFAddress1 – Gene Jun 29 '15 at 16:50

0 Answers0