Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Merge with duplicate rows Expand / Collapse
Author
Message
Posted Tuesday, November 6, 2012 5:50 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 42, Visits: 163
Hello all, I am running merge stored procedures against various staging and final tables in my database. I have one table that is giving me issues. When running the following merge statement I receive the common merge error "The MERGE statement attempted to UPDATE or DELETE the same row more than once..."

Here is my code:

/****** Object: StoredProcedure [dbo].[sp_Merge_AssetClassUDF] Script Date: 11/06/2012 16:15:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Merge_AssetClassUDF]
@PullDate Date

AS BEGIN



MERGE [AssetClassUDF] AS target
USING [RawData_AssetClassUDF] AS source
ON target.ASSETDETAIL_Id = source.ASSETDETAIL_Id
AND target.ASSET_UDF_DESC = source.ASSET_UDF_DESC




WHEN MATCHED THEN
UPDATE SET
[ASSET_UDF_VALUE] = source.[ASSET_UDF_VALUE],
[ASSET_UDF_DESC] = source.[ASSET_UDF_DESC],
[ASSETDETAIL_Id] = source.[ASSETDETAIL_Id],
Pulldate = @PullDate

WHEN NOT MATCHED THEN
INSERT (
[ASSET_UDF_VALUE],
[ASSET_UDF_DESC],
[ASSETDETAIL_Id],
Pulldate)
VALUES (
source.[ASSET_UDF_VALUE],
source.[ASSET_UDF_DESC],
source.[ASSETDETAIL_Id],
@PullDate);
END

ASSET_UDF_VALUE = user defined and many blank entries. Some duplicates.
ASSET_UDF_DESC = user defined and many duplicate values.
ASSETDETAIL_Id = provided by front end application and many duplicate values.
PullDate = Filled in by Stored procedure passing a getdate value

I think the above code breaks because the values in ASSET_UDF_DESC and ASSETDETAIL_Id are not unique. (I have lots more of these that work, but I have unique values to use and a source column in my merge statement)

I've tried lots of combinations of changing the target columns, but no luck!

Next is an output of a query of the data to give you an idea of what the table looks like. (any pointers how to copy and paste results from a query into here so it keeps its formatting?)


ASSET_UDF_VALUE	ASSET_UDF_DESC	ASSETDETAIL_Id	Pulldate
Filter 1 - Size/Description 16 2012-10-31
Filter 1 - Qty/Total 16 2012-10-31
Filter 2 - Size/Description 16 2012-10-31
Filter 2 - Qty/Total 16 2012-10-31
Belt Size/Description 16 2012-10-31
Refrigerant Type 16 2012-10-31
2/20X20X2 Filter 1 Size/Description 16 2012-10-31
Filter 2 Size/Description 16 2012-10-31
Refrigerant Capacity 16 2012-10-31
Nominal Tons 16 2012-10-31
Filter 1 - Size/Description 32 2012-10-31
Filter 1 - Qty/Total 32 2012-10-31
Filter 2 - Size/Description 32 2012-10-31
Filter 2 - Qty/Total 32 2012-10-31
Belt Size/Description 32 2012-10-31
Refrigerant Type 32 2012-10-31
2/20X20X2 Filter 1 Size/Description 32 2012-10-31
Filter 2 Size/Description 32 2012-10-31
1/A32 Belt Size/Description 32 2012-10-31
Refrigerant Capacity 32 2012-10-31
Nominal Tons 32 2012-10-31
Filter 1 - Size/Description 48 2012-10-31
Filter 1 - Qty/Total 48 2012-10-31
Filter 2 - Size/Description 48 2012-10-31
Filter 2 - Qty/Total 48 2012-10-31
Belt Size/Description 48 2012-10-31
Refrigerant Type 48 2012-10-31
Refrigerant Capacity 48 2012-10-31
Filter 1 Size/Description 48 2012-10-31
Filter 2 Size/Description 48 2012-10-31

Its hard to see but for asset-id 16 there are 10 or so duplicates. Each asset has the asset_udf_desc entry. Some of the Asset_UDF_Values have entries. As changes are made in the application, Values will be added as well as IDs.

Please let me know if there is anything you can recommend.
Post #1381759
Posted Tuesday, November 6, 2012 6:26 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
SQL-Squid,

You're correct in what you said the issue is that your source has duplicates, so based on the below you will always return multiple results, hence the MERGE statement returns that error.

MERGE [AssetClassUDF] AS target
USING [RawData_AssetClassUDF] AS source
ON target.ASSETDETAIL_Id = source.ASSETDETAIL_Id
AND target.ASSET_UDF_DESC = source.ASSET_UDF_DESC


Unfortunatley with the MERGE as the error says you cant update/insert the same record twice... so you only have 2 options.

1. use a GROUP BY in your MERGE so that way it will treat duplicates as One value & this should fix your problem (If this is what you want)

or

2. Clean the SOURCE so it doesn't contain duplicates.

I personally don't like Option 2 - Cleaning the source file as this is how it gets delivered and always good reference point when you want to compare RAW -> STAGING -> LIVE.
Post #1381763
Posted Wednesday, November 7, 2012 1:45 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Tava, you can insert the same record twice with a merge statement, heres a test rig.


Create Table #test_merge_target (
Col1 varchar(10)
,Col2 varchar(10)
)

Create Table #test_merge_source (
Col1 varchar(10)
,Col2 varchar(10)
)


Insert into #test_merge_source
values ('Acol','test data')
,('Acol','test data')
,('bcol','test data')


Merge into
#test_merge_target as target
USING #test_merge_source as Source
ON target.Col1=Source.Col1
WHEN MATCHED THEN
Update Set target.Col2=Source.Col2
When NOT MATCHED Then
INSERT (Col1,Col2)
VALUES (Source.Col1,Source.Col2);

Select * from #test_merge_target

GO
drop table #test_merge_source
drop table #test_merge_target


however you cannot UPDATE or DELETE the Same row twice.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1381825
Posted Wednesday, November 7, 2012 2:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, May 31, 2014 9:19 PM
Points: 1,128, Visits: 1,162
Jason-299789 (11/7/2012)
Tava, you can insert the same record twice with a merge statement, heres a test rig.


Create Table #test_merge_target (
Col1 varchar(10)
,Col2 varchar(10)
)

Create Table #test_merge_source (
Col1 varchar(10)
,Col2 varchar(10)
)


Insert into #test_merge_source
values ('Acol','test data')
,('Acol','test data')
,('bcol','test data')


Merge into
#test_merge_target as target
USING #test_merge_source as Source
ON target.Col1=Source.Col1
WHEN MATCHED THEN
Update Set target.Col2=Source.Col2
When NOT MATCHED Then
INSERT (Col1,Col2)
VALUES (Source.Col1,Source.Col2);

Select * from #test_merge_target

GO
drop table #test_merge_source
drop table #test_merge_target


however you cannot UPDATE or DELETE the Same row twice.


one way to aviod this is to use unique condition for the rowset;
i.e if unique column exist then add that in the NOT MATCHED condition ...


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1381851
Posted Wednesday, November 7, 2012 3:07 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Agreed demon, a unique constraing on the target table is the only way to prevent this happening.

I've had a look at the data the op put up and I cant see any duplicates on in the set based on the DESC & ID columns.

One thing the OP could do is a Distinct/group by on all columns in a CTE and use that as the Source rather than the raw table.

Personally, I'd throw this back up to whoever generated the file and ask them to explain wht there are duplicate rows, and remove them if possible, or look at altering the extract to make the rows more unique.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1381857
Posted Wednesday, November 7, 2012 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 7,120, Visits: 13,491
-- Have a look at the dupesets in the source table - do you want them, or not?
-- Then decide what to do. No point in finding a clever way to
-- insert the dupes if you don't want them.
SELECT *
FROM (
SELECT
*,
Dupecount = COUNT(*) OVER(PARTITION BY ASSETDETAIL_Id, ASSET_UDF_DESC)
FROM [RawData_AssetClassUDF]
) d
WHERE Dupecount > 1
ORDER BY ASSETDETAIL_Id, ASSET_UDF_DESC



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1381959
Posted Wednesday, November 7, 2012 10:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 42, Visits: 163
Thanks all. I will have a go with group by in my merge and see if I can get the results I am after.

The source data is a feed that I cannot control and the duplicates are in fact needed by design. (they wont make modifications to the source application for my needs)

Removing duplicates based on ID will eliminate rows I need. Inserts work as others have posted, its the update that gets me. As said, I will see if I can figure out the group by clause and post back my results.

Thanks again everyone!
Post #1382084
Posted Wednesday, November 7, 2012 11:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 42, Visits: 163
Here is an attempt to use a CTE and I am lost again, not sure if this is going to work as I expected. Code below throws the same merge error as before.

I changed the group by columns and the on "target" columns around a few times and still I am not getting this to work...

Maybe its something simple I am missing?

ALTER PROCEDURE [dbo].[sp_Merge_AssetClassUDF_V2]
@PullDate Date

AS BEGIN



MERGE [AssetClassUDF] AS target
USING (
SELECT ASSET_UDF_VALUE, ASSET_UDF_DESC, ASSETDETAIL_Id
FROM AssetClassUDF
GROUP BY ASSET_UDF_DESC,ASSET_UDF_VALUE)
AS AssetClassUDF_CTE (ASSET_UDF_VALUE, ASSET_UDF_DESC, ASSETDETAIL_Id)

ON

(target.ASSETDETAIL_Id = AssetClassUDF_CTE.ASSETDETAIL_Id
AND target.ASSET_UDF_DESC = AssetClassUDF_CTE.ASSET_UDF_DESC)




WHEN MATCHED THEN
UPDATE SET
[ASSET_UDF_VALUE] = AssetClassUDF_CTE.[ASSET_UDF_VALUE],
[ASSET_UDF_DESC] = AssetClassUDF_CTE.[ASSET_UDF_DESC],
[ASSETDETAIL_Id] = AssetClassUDF_CTE.[ASSETDETAIL_Id],
Pulldate = @PullDate

WHEN NOT MATCHED THEN
INSERT (
[ASSET_UDF_VALUE],
[ASSET_UDF_DESC],
[ASSETDETAIL_Id],
Pulldate)
VALUES (
AssetClassUDF_CTE.[ASSET_UDF_VALUE],
AssetClassUDF_CTE.[ASSET_UDF_DESC],
AssetClassUDF_CTE.[ASSETDETAIL_Id],
@PullDate);
END

Would I be better to run separate update and inserts and use a CTE to delete duplicates? Basically avoiding Merge's logic altogether?

At the end of the day I need to update the asset_udf_value when it changes for an existing asset_id (update) and then insert new rows when new data is added from the feed. (simple insert)
Post #1382106
Posted Wednesday, November 7, 2012 12:07 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 42, Visits: 163
Here is the data to test with:

CREATE TABLE [dbo].[AssetClassUDF](
[ASSET_UDF_VALUE] [nvarchar](255) NULL,
[ASSET_UDF_DESC] [nvarchar](255) NULL,
[ASSETDETAIL_Id] [numeric](20, 0) NULL,
[Pulldate] [date] NULL
) ON [PRIMARY]


GO

INSERT INTO ASSETCLASSUDF
(ASSET_UDF_VALUE,ASSET_UDF_DESC,ASSETDETAIL_Id,Pulldate)
SELECT '','Filter 1 - Size/Description','16','2012-10-31' Union All
SELECT '','Filter 1 - Qty/Total ','16','2012-10-31' Union All
SELECT '','Filter 2 - Size/Description ','16','2012-10-31' Union All
SELECT '','Filter 2 - Qty/Total','16','2012-10-31' Union All
SELECT '','Belt Size/Description','16','2012-10-31' Union All
SELECT '','Refrigerant Type','16','2012-10-31' Union All
SELECT '','Refrigerant Capacity','16','2012-10-31' Union All
SELECT '2/20X20X2','Filter 1 Size/Description','16','2012-10-31' Union All
SELECT '','Filter 2 Size/Description','16','2012-10-31' Union All
SELECT '','Belt Size/Description','16','2012-10-31' Union All
SELECT '','Refrigerant Type','16','2012-10-31' Union All
SELECT '','Refrigerant Capacity','16','2012-10-31' Union All
SELECT '','Nominal Tons','16','2012-10-31' Union All
SELECT '','Filter 1 - Size/Description','32','2012-10-31' Union All
SELECT '','Filter 1 - Qty/Total ','32','2012-10-31' Union All
SELECT '','Filter 2 - Size/Description ','32','2012-10-31' Union All
SELECT '','Filter 2 - Qty/Total','32','2012-10-31' Union All
SELECT '','Belt Size/Description','32','2012-10-31' Union All
SELECT '','Refrigerant Type','32','2012-10-31' Union All
SELECT '','Refrigerant Capacity','32','2012-10-31' Union All
SELECT '2/20X20X2','Filter 1 Size/Description','32','2012-10-31' Union All
SELECT '','Filter 2 Size/Description','32','2012-10-31' Union All
SELECT '1/A32','Belt Size/Description','32','2012-10-31' Union All
SELECT '','Refrigerant Type','32','2012-10-31' Union All
SELECT '','Refrigerant Capacity','32','2012-10-31' Union All
SELECT '','Nominal Tons','32','2012-10-31' Union All
SELECT '','Filter 1 - Size/Description','48','2012-10-31' Union All
SELECT '','Filter 1 - Qty/Total ','48','2012-10-31' Union All
SELECT '','Filter 2 - Size/Description ','48','2012-10-31' Union All
SELECT '','Filter 2 - Qty/Total','48','2012-10-31' Union All
SELECT '','Belt Size/Description','48','2012-10-31' Union All
SELECT '','Refrigerant Type','48','2012-10-31' Union All
SELECT '','Refrigerant Capacity','48','2012-10-31' Union All
SELECT '','Filter 1 Size/Description','48','2012-10-31' Union All
SELECT '','Filter 2 Size/Description','48','2012-10-31' Union All
SELECT '','Belt Size/Description','48','2012-10-31' Union All
SELECT '','Refrigerant Type','48','2012-10-31' Union All
SELECT '','Refrigerant Capacity','48','2012-10-31' Union All
SELECT '','Nominal Tons','48','2012-10-31' Union All
SELECT '','Filter 1 - Size/Description','64','2012-10-31'


Thanks to the Forum Etiquette guide here: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1382118
Posted Wednesday, November 7, 2012 3:38 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, April 27, 2014 8:38 PM
Points: 90, Visits: 420
Jason-299789 (11/7/2012)
Tava, you can insert the same record twice with a merge statement, however you cannot UPDATE or DELETE the Same row twice.


Yes Sorry, I meant Update/Delete not Update/Insert...
Post #1382207
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse