SQL Merge update

my merge query

  MERGE Povertytgt t 
    USING Povertyrsc s
        ON (s.Incomegroup = t.Incomegroup )
 WHEN MATCHED
    THEN UPDATE SET 
        t.CountbyGroup = s.CountbyGroup;

and got 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.

this is resource table

CountbyGroup    IncomeGroup ID
78              Limit 2 - $4,012    
17              Limit 2 - $4,012

This is target table

CountbyGroup    IncomeGroup       
0               Limit 2 - $4,012     
0               Limit 3 - $4,956     
0               Limit 4 - $5,899

I expect this result

CountbyGroup    IncomeGroup 
78              Limit 2 - $4,012    
17              Limit 3 - $4,956    
0               Limit 4 - $5,899

Answer

Why not just use update?

UPDATE t
    SET t.CountbyGroup = s.CountbyGroup;
    FROM Povertytgt t JOIN
         Povertyrsc s
         ON s.Incomegroup = t.Incomegroup;

You don’t specify the error, so I don’t know if this will fix it. But it is a simpler approach.

and got error

Advertisement

Answer