Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Merge statement reporting an error with multiple rows existing

Posted on 2013-12-29
6
Medium Priority
?
2,992 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

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 2000 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

730 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