Solved

Merge statement reporting an error with multiple rows existing

Posted on 2013-12-29
6
2,731 Views
Last Modified: 2014-08-25
USE [EnergySuiteDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_ManualMeterReadings_BulkUploadMerge]    Script Date: 12/29/2013 07:07:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ManualMeterReadings_BulkUploadMerge]
    (
      @MeterID INT 
    )
AS 
    BEGIN
    DECLARE @retValue INT
    declare @dt datetime
    declare @matched INT
        BEGIN TRY
            IF OBJECT_ID('ManualMeterReadings') IS NOT NULL 
                BEGIN
                    BEGIN TRANSACTION MergManualMeterReadingsTable
                    SET NOCOUNT ON;
                    MERGE dbo.ManualMeterReadings AS target
                        USING 
                            ( SELECT    PU.MeterID,
                                PU.MPAN ,
                                PU.MeterReference ,
                                PU.MeterReference2 ,
                                PU.MeterReading ,
                                PU.PeakDemand ,
                                PU.Offset ,
                                PU.PowerFactor ,
                                PU.ReactiveConsumption ,
                                PU.ReadingType ,
                                PU.Consumption ,
                                PU.Target ,
                                PU.TargetDifference ,
                                PU.Timestamp ,
                                PU.ActualkVA ,
                                PU.AvailablekVA ,
                                PU.Barcode 

                              FROM      dbo.tempManualMeterReadings PU
                              WHERE     PU.MeterID = @MeterID
                              GROUP BY  PU.MeterID,
							    PU.MPAN ,
                                PU.MeterReference ,
                                PU.MeterReference2 ,
                                PU.MeterReading ,
                                PU.PeakDemand ,
                                PU.Offset ,
                                PU.PowerFactor ,
                                PU.ReactiveConsumption ,
                                PU.ReadingType ,
                                PU.Consumption ,
                                PU.Target ,
                                PU.TargetDifference ,
                                PU.Timestamp ,
                                PU.ActualkVA ,
                                PU.AvailablekVA ,
                                PU.Barcode 

                            ) AS source (MeterID, MPAN ,
                                MeterReference ,
                                MeterReference2 ,
                                MeterReading ,
                                PeakDemand ,
                                Offset ,
                                PowerFactor ,
                                ReactiveConsumption ,
                                ReadingType ,
                                Consumption ,
                                Target ,
                                TargetDifference ,
                                Timestamp ,
                                ActualkVA ,
                                AvailablekVA ,
                                Barcode  )
                        ON ( (target.MeterID) = (source.MeterID)
                             AND (target.Timestamp) = (source.Timestamp)
                           )
                        WHEN MATCHED 
                            THEN 
        UPDATE                SET
                @dt=source.timestamp,
                Consumption = source.Consumption ,
                MeterReading = source.MeterReading,               
                ReactiveConsumption=source.ReactiveConsumption,
				ActualkVA=source.ActualkVA,
				AvailablekVA=source.AvailablekVA
                        WHEN NOT MATCHED 
                            THEN 
        INSERT  (
                  MeterID, MPAN ,
                                MeterReference ,
                                MeterReference2 ,
                                MeterReading ,
                                PeakDemand ,
                                Offset ,
                                PowerFactor ,
                                ReactiveConsumption ,
                                ReadingType ,
                                Consumption ,
                                Target ,
                                TargetDifference ,
                                Timestamp ,
                                ActualkVA ,
                                AvailablekVA ,
                                Barcode
                )             VALUES
                ( source.MeterID, source.MPAN ,
                                source.MeterReference ,
                                source.MeterReference2 ,
                                source.MeterReading ,
                                source.PeakDemand ,
                                source.Offset ,
                                source.PowerFactor ,
                                source.ReactiveConsumption ,
                                source.ReadingType ,
                                source.Consumption ,
                                source.Target ,
                                source.TargetDifference ,
                                source.Timestamp ,
                                source.ActualkVA ,
                                source.AvailablekVA ,
                                source.Barcode
                );
                    DELETE  PU
                    FROM    dbo.tempManualMeterReadings PU
                    WHERE   PU.MeterID = @MeterID
                    COMMIT TRANSACTION MergManualMeterReadingsTable
                    SET @retValue = 1
                    SELECT @retValue
                END
            ELSE 
                BEGIN
                SET @retValue = -1
                    SELECT  @retValue
                END
        END TRY
        BEGIN CATCH
            ROLLBACK TRANSACTION MergManualMeterReadingsTable
            DECLARE @ErrorMsg VARCHAR(MAX);
            DECLARE @ErrorSeverity INT;
            DECLARE @ErrorState INT;
            SET @ErrorMsg = ERROR_MESSAGE();
            SET @ErrorSeverity = ERROR_SEVERITY();
            SET @ErrorState = ERROR_STATE();
        SET @retValue = 1000
        SELECT @retValue
           -- SELECT  0 AS isSuccess
        END CATCH
    END

Open in new window

Hi,

I'm trying to use the attached merge statement to insert or update data to my table. The insert works well but the update always returns an error that multiple rows exist, meaning it suggests I have multiple rows in the tempManualMeterReadings tables where MeterID and Timestamp are the same.

I've attached the source tempManualMeterReadings table data and the target ManualMeterReadings table data. I can't see where the duplication exists, there should be only one match for each row.

Any idea where I'm going wrong here?

[embed=snippet 8258877
Source.txt
Target.txt
0
Comment
Question by:wint100
6 Comments
 
LVL 38

Expert Comment

by:Aaron Tomosky
ID: 39745156
Hard to look at the .txt on my phone, but are there nulls hiding in there? Those will usually cause that sort of problem
0
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39748880
Hi,

I imported your tables into a test database, ran your merge and got this 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.

Please run this query:
use ExpertsExchange
go

select 
	MeterID
	, Timestamp
	, count( * )	
from dbo.ManualMeterReadings
group by
	MeterID
	, Timestamp
having
	count( * ) > 1
;

select 
	MeterID
	, Timestamp
	, count( * )	
from dbo.tempManualMeterReadings
group by
	MeterID
	, Timestamp
having
	count( * ) > 1
;

Open in new window

Which produces

MeterID                Timestamp               
---------------------- ----------------------- -----------
43                     2012-10-28 01:30:00.000 2
43                     2013-10-27 01:30:00.000 2
43                     2013-10-27 01:00:00.000 2
43                     2012-10-28 01:00:00.000 2

(4 row(s) affected)

MeterID                Timestamp               
---------------------- ----------------------- -----------
43                     2013-10-27 01:30:00.000 2
43                     2013-10-27 01:00:00.000 2

(2 row(s) affected)

Open in new window

As you can see, you have duplicates in your data.

HTH
  David
0
 
LVL 1

Author Comment

by:wint100
ID: 39778683
Hi,

Sorry for the delay. Thanks a lot for this query and pointing out my error in thinking there were no duplicate rows. I've now modified the query to only insert when the data isn't already present, and ignore existing data.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Expert Comment

by:adrian lujan
ID: 40284276
Can I get an example of the code to fix msg 8672
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40284582
Hi,

What is the error message?

And you should post this in another question.

Regards
  David
0
 

Expert Comment

by:adrian lujan
ID: 40284719
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.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now