Merge with duplicate rows

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Merge with duplicate rows

16 posts, Page 1 of 2

1 2 »»

Merge with duplicate rows

Rate Topic

Display Mode

Topic Options

Author

Message

SQL-Squid

Posted Tuesday, November 06, 2012 5:50 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 5:06 PM
Points: 39, Visits: 129

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

Tava

Posted Tuesday, November 06, 2012 6:26 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 11:58 PM
Points: 76, Visits: 297

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

Jason-299789

Posted Wednesday, November 07, 2012 1:45 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 803, Visits: 2,124

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

demonfox

Posted Wednesday, November 07, 2012 2:44 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, May 18, 2013 6:46 PM
Points: 1,074, Visits: 1,076

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 Ermm

Post #1381851

Jason-299789

Posted Wednesday, November 07, 2012 3:07 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:30 AM
Points: 803, Visits: 2,124

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

ChrisM@Work

Posted Wednesday, November 07, 2012 6:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:27 AM
Points: 5,618, Visits: 10,990

-- 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

SQL-Squid

Posted Wednesday, November 07, 2012 10:37 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 5:06 PM
Points: 39, Visits: 129

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

SQL-Squid

Posted Wednesday, November 07, 2012 11:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 5:06 PM
Points: 39, Visits: 129

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

SQL-Squid

Posted Wednesday, November 07, 2012 12:07 PM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 5:06 PM
Points: 39, Visits: 129

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

Tava

Posted Wednesday, November 07, 2012 3:38 PM

SSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 11:58 PM
Points: 76, Visits: 297

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 »

16 posts, Page 1 of 2

1 2 »»

Permissions