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!

score 145 · Accepted Answer · edited Jan 31 '19 at 06:23

145

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

edited Jan 31 '19 at 06:23

Aman Rawat

2,625
1
25
40

answered Mar 27 '12 at 18:25

jerry

1,817
1
11
6

37

This doesn't seem to work on MySQL. The correct syntax there would be `UPDATE DHE.dbo.tblAccounts A INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink RA ON A.AccountCode = RA.AccountCode SET ControllingSalesRep = RA.SalesRepCode`. – user1111929 Jan 30 '15 at 21:04
That's right... the answer above (proposed by @jerry) does not work for mysql. – htobon May 26 '15 at 16:42
personally I don't like using aliasing unless it is necessary, like when there are lots of fields or tables involved, etc. But then I do still qualify each field with tablename.fieldname for clarity even without aliasing – AquaAlex Oct 25 '21 at 16:13

score 35 · Answer 2 · edited Oct 18 '17 at 19:01

35

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

edited Oct 18 '17 at 19:01

Gabriel Dal Carobo

3
4

answered Feb 10 '14 at 06:02

ram

2,275
3
27
38

score 6 · Answer 3 · edited Dec 19 '12 at 12:44

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

score 5 · Answer 4 · answered Mar 27 '12 at 17:21

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.

score 4 · Answer 5 · answered Dec 10 '15 at 13:15

4

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

answered Dec 10 '15 at 13:15

Sandra

61
6

score 1 · Answer 6 · edited May 09 '16 at 17:20

1

which may be useful

Update
    A INNER JOIN B ON A.COL1=B.COL3
SET
    A.COL2='CHANGED', A.COL4=B.COL4,......
WHERE ....;

edited May 09 '16 at 17:20

Geeky Guy

9,229
4
42
62

answered Apr 28 '14 at 07:20

Mahendra Jella

5,450
1
33
38

score 1 · Answer 7 · edited Jul 20 '20 at 12:40

1

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";

edited Jul 20 '20 at 12:40

Dharman

30,962
25
85
135

answered Jul 20 '20 at 12:34

vishwampandya

1,067
11
11

score 0 · Answer 8 · edited Oct 25 '14 at 20:53

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

You should paste a summary of the provided link in your post to prevent deadlinking — foibs, Jan 09 '14 at 13:25

score 0 · Answer 9 · answered Aug 29 '15 at 07:26

//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 = '')

score 0 · Answer 10 · answered Dec 03 '15 at 05:03

0

Update one table using Inner Join

  UPDATE Table1 SET name=ml.name
FROM table1 t inner JOIN
Table2 ml ON t.ID= ml.ID

answered Dec 03 '15 at 05:03

kavitha Reddy

3,303
24
14

score 0 · Answer 11 · edited Mar 19 '19 at 08:57

0

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;

edited Mar 19 '19 at 08:57

taras

6,566
10
39
50

answered Mar 19 '19 at 08:53

Vivek Bhandari

101
4

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

11 Answers11

Linked