使用一个服务器上两个不同数据库中的表之间的内部连接更新查询

Need some SQL syntax help :-)

需要一些SQL语法帮助:-)

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.

我可以用类似语法的链接表进行查询——但SQL不喜欢这样。

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

我相信这是一个简单的问题:-D

Thanks!

谢谢!

9 个解决方案

#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

#2

Following is the MySQL syntax:

以下是MySQL语法:

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

#3

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.

对不起，已经很晚了，但我想这对那些来到这里寻找类似问题解决方案的人是有帮助的。set子句应该在update子句之后出现。因此，只需稍作更改就可以重新排列查询。

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

#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

直到语法工作-MySQL。

#5

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.

更新表在FROM子句中重复。

#6

It is explained here http://erabhinavrana.blogspot.in/2014/01/how-to-execute-update-query-by-applying.html

这里解释为http://erabhinavrana.blogspot.in4//20101/how -execute-update- queryby 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

http://www.dynamic-coders.com/how-to-update-two-different-tables-in-different-databases-on-same-server

#7

which may be useful

这可能是有用的

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

#8

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

#9

Update one table using Inner Join

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

#1