Update Query with INNER JOIN between tables in 2 different databases on 1 server

Question

Need some SQL syntax help :-)

Both databases are on the same server

db1 = DHE db2 = DHE_Import  UPDATE DHE.dbo.tblAccounts  INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink      ON DHE.dbo.tblAccounts.AccountCode = DHE_Import.tblSalesRepsAccountsLink.AccountCode  SET DHE.dbo.tblAccounts.ControllingSalesRep = DHE_Import.dbo.tblSalesRepsAccountsLink.SalesRepCode 

I can do a query in Access with linked tables with similar syntax - BUT SQL doesn't like it.

I'm sure it's a simple issue :-D

Thanks!

Answer 1

You could call it just style, but I prefer aliasing to improve readability.

UPDATE A       SET ControllingSalesRep = RA.SalesRepCode    from DHE.dbo.tblAccounts A   INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink RA     ON A.AccountCode = RA.AccountCode 

For MySQL

UPDATE DHE.dbo.tblAccounts A    INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink RA        ON A.AccountCode = RA.AccountCode  SET A.ControllingSalesRep = RA.SalesRepCode