CODEWITHSUNDEEP
sqlsql-server-2008t-sqlmerge
user1745767
user1745767

Reputation: 1071

MERGE statement failing after 2 years of working

My script has been using a merge statement for a few years, every day, but on the 6th October it stopped working and is throwing an error:

Msg 8672, Level 16, State 1, Line 1
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.

My script looks like:

MERGE INTO dbo.energydata AS target
USING dbo.temp_energydata AS source ON target.webmeterID = source.webmeterID
                                    AND target.DateTime = source.DateTime
WHEN MATCHED THEN 
   UPDATE 
      SET target.kWh = source.kWh

WHEN NOT MATCHED BY TARGET THEN
   INSERT (webmeterID, DateTime, kWh)
   VALUES (source.webmeterID, source.DateTime, source.kWh);

It basically takes data from a temp_table (temp_energydata) and merges it with the main table (energydata). It uses merge because the temp table contains duplicate data each day, and the MERGE would prevent the duplication errors.

I realise I'm going to have to provide more info, so if you could let me know how to debug this, that will be great.

Upvotes: 0

Views: 1094

Answers (1)

radar
radar

Reputation: 13425

You have duplicate entries in your temp table

Run this query to get those duplicates

  Select webmeterID, [DateTime]
  From dbo.temp_energydata
  Group by webmeterID, [DateTime]
  Having count(*) > 1

Upvotes: 2

Related Questions