1

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
  • 1
    When you say OPENQUERY - are you trying to join to another server, or what kind of data source are you working with there? – Brent Ozar Apr 30 '18 at 22:07
  • @BrentOzar -> I am pulling data from a PostgreSQL server over to my SQL Server install. I am unable to set it as a linked server and using OPENQUERY is the only solution I have found that would allow me to query the PostgreSQL server. – user2932408 Apr 30 '18 at 22:12
  • 1
    Is the ON missing by accident? Plus you have xyz alias twice. It only needs to be after the derived table, not before. – ypercubeᵀᴹ Apr 30 '18 at 22:17
  • @ypercubeᵀᴹ - ah yes, good catch. I am still trying to learn the SSMS syntax. – user2932408 Apr 30 '18 at 22:17
3

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 
;
  • Is INNER JOIN what I am after here? I want to update all of the accID in table mi. Table sv has roughly 10K records, and mi only has < 1K - would it be quicker if I change the join? – user2932408 Apr 30 '18 at 22:31
  • 1
    How are you supposed to match rows from the two tables? Are the rows of mi to be matched with the rows from sv via the accID columns? Or something else? – ypercubeᵀᴹ Apr 30 '18 at 22:33
  • 1
    Is accID the primary or a unique key of masterinfo? – ypercubeᵀᴹ Apr 30 '18 at 22:34
  • accID is a unique key, and they match the records in the two tables. – user2932408 Apr 30 '18 at 22:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.