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

User · Answer

It is explained here http   erabhinavrana blogspot in 2014 01 how-to-execute-update-query-by-applying html  It also has other useful code snippets which are commonly used   update  lt dbname of 1st table gt   lt table name of 1st table gt  A INNER JOIN  lt dbname of 2nd table gt   lt table name of 2nd table gt  RA ON A  lt field name of table 1 gt  RA  lt field name of table 2 gt  SET A  lt field name of table 1 to be updated gt  RA  lt field name of table 2 to set value in table 1 gt    Replace data in  lt  gt  with your appropriate values   That s It  source       http   www dynamic-coders com how-to-update-two-different-tables-in-different-databases-on-same-server

User · Answer

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

User · Answer

UPDATE table1 a  inner join  table2 b on  a kol1 a b kol1     SET a kol1 b kol1 WHERE  a kol1          for me until the syntax worked -MySQL

User · Answer

Following is the  MySQL syntax   UPDATE table1  INNER JOIN table2 ON table1 field1   table2 field2 SET table1 field3   table2 field4  WHERE            http   geekswithblogs net faizanahmad archive 2009 01 05 join-in-sql-update--statement aspx

User · Answer

For Access Database  UPDATE   tblEmployee LEFT JOIN tblCity ON  tblEmployee CityCode   tblCity CityCode   LEFT JOIN tblCountry ON  tblEmployee CountryCode   tblCountryCode   SET tblEmployee CityName   tblCity CityName   tblEmployee CountryName   tblCountry CountryName WHERE  tblEmployee CityName      OR tblEmployee CountryName

User · Answer

Should look like this   UPDATE DHE dbo tblAccounts    SET DHE dbo tblAccounts ControllingSalesRep           DHE Import dbo tblSalesRepsAccountsLink SalesRepCode   from DHE dbo tblAccounts       INNER JOIN DHE Import dbo tblSalesRepsAccountsLink          ON DHE dbo tblAccounts AccountCode              DHE Import tblSalesRepsAccountsLink AccountCode    Update table is repeated in FROM clause

User · Answer

It is very simple to update using Inner join query in SQL  You can do it without using FROM clause  Here is an example       UPDATE customer table c         INNER JOIN             employee table e           ON  c city id   e city id         SET c active    quot Yes quot       WHERE c city    quot New york quot

User · Answer

Worked perfectly for me   UPDATE TABLE A a INNER JOIN TABLE B b ON a col1   b col2 SET a col which you want update   b col from which you update

User · Answer

which may be useful  Update     A INNER JOIN B ON A COL1 B COL3 SET     A COL2  CHANGED   A COL4 B COL4        WHERE

User · Answer

Update one table using Inner Join    UPDATE Table1 SET name ml name FROM table1 t inner JOIN Table2 ml ON t ID  ml ID

User · Answer

Sorry its late  but I guess it would be of help to those who land here finding a solution to similar problem   The set clause should come right after the update clause  So rearranging your query with a bit change does the work   UPDATE DHE dbo tblAccounts  SET DHE dbo tblAccounts ControllingSalesRep       DHE Import dbo tblSalesRepsAccountsLink SalesRepCode from DHE dbo tblAccounts  INNER JOIN DHE Import dbo tblSalesRepsAccountsLink      ON DHE dbo tblAccounts AccountCode           DHE Import tblSalesRepsAccountsLink AccountCode

[sql-server] Update Query with INNER JOIN between tables in 2 different databases on 1 server

Examples related to sql-server

Examples related to database

Examples related to syntax

Examples related to inner-join