How do I use the GROUP BY clause in a SQL MERGE statement?

group-by, merge, sql, t-sql

Solution

Something like this:

MERGE dbo.MyTarget targ
USING (SELECT ... FROM dbo.MySource GROUP BY .....) src
ON (targ.Identifier = src.Identifier
    AND targ.Name = src.ConstituentName
    AND targ.Ticker = src.ConstituentTicker
    AND (targ.CUSIP = src.CUSIP OR targ.ISIN = src.ISIN OR targ.SEDOL = src.SEDOL))
WHEN MATCHED THEN
-- update values
;

Problem

I am trying to merge two tables and I would like to use GROUP BY in order to fix the following error: `The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.` Where exactly would the GROUP BY clause go? ``` MERGE dbo.MyTarget targ USING dbo.MySource src ON (targ.Identifier = src.Identifier AND targ.Name = src.ConstituentName AND targ.Ticker = src.ConstituentTicker AND (targ.CUSIP = src.CUSIP OR targ.ISIN = src.ISIN OR targ.SEDOL = src.SEDOL)) WHEN MATCHED THEN -- update values ; ```

Original source