Update Query with INNER JOIN between tables in 2 different databases on 1 server
Sql ServerDatabaseSyntaxInner JoinSql Server Problem Overview
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!
Sql Server Solutions
Solution 1 - Sql Server
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
Solution 2 - Sql Server
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
Solution 3 - Sql Server
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
Solution 4 - Sql Server
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
Solution 5 - Sql Server
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.
Solution 6 - Sql Server
which may be useful
Update
A INNER JOIN B ON A.COL1=B.COL3
SET
A.COL2='CHANGED', A.COL4=B.COL4,......
WHERE ....;
Solution 7 - Sql Server
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 = "Yes"
WHERE c.city = "New york";
Solution 8 - Sql Server
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 <dbname of 1st table>.<table name of 1st table> A INNER JOIN <dbname of 2nd table>.<table name of 2nd table> RA ON A.<field name of table 1>=RA.<field name of table 2> SET A.<field name of table 1 to be updated>=RA.<field name of table 2 to set value in table 1>
Replace data in <>
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
Solution 9 - Sql Server
//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 = '')
Solution 10 - Sql Server
Update one table using Inner Join
UPDATE Table1 SET name=ml.name
FROM table1 t inner JOIN
Table2 ml ON t.ID= ml.ID
Solution 11 - Sql Server
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;