Sql-Server
幫助使用 JOIN 編寫 UPDATE 語句
我是 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 ;