sql-server - sql多次join - sql多表连接



在1台服务器上的2个不同数据库中的表之间使用INNER JOIN更新查询 (7)

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

两个数据库都在同一台服务器上

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

我可以在Access中使用具有类似语法的链接表进行查询 - 但是SQL不喜欢它。

我确定这是一个简单的问题:-D

谢谢!


Answer #1

为我工作完美。

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;


Answer #3

应该是这样的:

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 

在FROM子句中重复更新表。


Answer #4

您可以将其称为样式,但我更喜欢使用别名来提高可读性。

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

对于MySQL

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

Answer #5

这里解释了http://erabhinavrana.blogspot.in/2014/01/how-to-execute-update-query-by-applying.html

它还有其他常用的有用代码片段。

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>

用适当的值替换<>数据。

而已。 资源:

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


Answer #6
UPDATE table1 a
 inner join  table2 b on (a.kol1=a.b.kol1...)
SET a.kol1=b.kol1
WHERE 
a.kol1='' ...

对我来说,直到语法工作--MySQL


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




inner-join