Help Writing an UPDATE Statement With JOIN I am new to SQL Server and am attempting to write an UPDATE statement that includes a JOIN. Essentially this is what I am trying to write, but obviously this is invalid syntax.  In SQL Server 2008 R2 what would be the appropriate way to write this syntax?

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
I think you just missed the ON clause and placed the xyz alias twice. Corrected:

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
 ;




Once - and if - you are sure that the above is correct (from the point of what exactly you want to do), you may try to do the aggregation in the remote server. It might be more efficient:

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 
;


and I think this one can be simplified a bit:

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 
;