Sql-Server

幫助使用 JOIN 編寫 UPDATE 語句

  • May 1, 2018

我是 SQL Server 的新手,正在嘗試編寫一個包含 JOIN 的 UPDATE 語句。本質上這是我要寫的,但顯然這是無效的語法。在 SQL Server 2008 R2 中,編寫此語法的合適方法是什麼?

Update mi
Set totalvalue = TV
FROM masterinfo mi
Inner Join xyz
(
 Select accID
 ,SUM(totalval) TV
 from OPENQUERY(EA, 'Select accID, totalval from sv')
 GROUP BY accID    
) xyz

我認為您只是錯過了該ON子句併兩次放置了xyz別名。更正:

UPDATE mi
SET totalvalue = xyz.TV
FROM masterinfo mi
 INNER JOIN                     -- removed incorrectly placed alias xyz
 (
   SELECT accID, SUM(totalval) TV
   FROM OPENQUERY(EA, 'select accID, totalval from sv')
   GROUP BY accID    
 ) xyz                          -- correct here
 ON xyz.accID = mi.accID        -- missing ON clause, adjust 
                                -- according to your structure
;

一旦 - 如果 - 您確定以上內容是正確的(從您想要做的事情來看),您可以嘗試在遠端伺服器中進行聚合。它可能更有效:

UPDATE mi
SET totalvalue = xyz.TV
FROM masterinfo mi
 INNER JOIN 
 (
   SELECT accID, TV
   FROM OPENQUERY(EA, 'select accID, sum(totalval) TV from sv group by accID')
 ) xyz
 ON xyz.accID = mi.accID 
;

我認為這個可以簡化一點:

UPDATE mi
SET totalvalue = xyz.TV
FROM masterinfo mi
 INNER JOIN 
    OPENQUERY(EA, 'select accID, sum(totalval) TV from sv group by accID') xyz
 ON xyz.accID = mi.accID 
;

引用自:https://dba.stackexchange.com/questions/205401