Delete query remove duplicate rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete query remove duplicate rows

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-09 : 09:57:23

I have multiple records with the same info in the master table:

keep one and delete the rest, is it possible based on two field condition/combination.

Delete from tab_EmailDownloadMaster where RmTitle is same for more than one record andalso ReceivedDate date/timestamp including seconds is same for more than one record.

Thank you very much for the helpful info.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-09 : 10:00:46

Get idea from point 6
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-09 : 10:04:04

Or see this:
http://weblogs.sqlteam.com/mladenp/archive/2009/01/05/The-simplest-way-to-delete-duplicates-and-compare-two-result.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 10:58:26

quote:
Originally posted by webfred

Or see this:
http://weblogs.sqlteam.com/mladenp/archive/2009/01/05/The-simplest-way-to-delete-duplicates-and-compare-two-result.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

Have you read the comment section of that page webfred? Specifically this:

quote:
The Microsoft CHECKSUM is a very, VERY, weak algorithm as proved in the links above.
It just a 32-bit value XOR'ed with previous value shifted 4 bits.

In other words, if you have a table with 4 million unique rows, then the CHECKSUM method has about a 1 in 1000 chance of deleting the wrong record. Many would say this is too much of a risk, especially when there are methods that will do the same job with no risk at all.

The code I posted the other day (the one you picked up the syntax errors on), is a much more reliable method.

cplusplus, can you post some sample data?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-09 : 11:10:09

quote:
Originally posted by DBA in the making

quote:
Originally posted by webfred

Or see this:
http://weblogs.sqlteam.com/mladenp/archive/2009/01/05/The-simplest-way-to-delete-duplicates-and-compare-two-result.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

Have you read the comment section of that page webfred? Specifically this:
quote:
The Microsoft CHECKSUM is a very, VERY, weak algorithm as proved in the links above.
It just a 32-bit value XOR'ed with previous value shifted 4 bits.

In other words, if you have a table with 4 million unique rows, then the CHECKSUM method has about a 1 in 1000 chance of deleting the wrong record. Many would say this is too much of a risk, especially when there are methods that will do the same job with no risk at all.

The code I posted the other day (the one you picked up the syntax errors on), is a much more reliable method.

cplusplus, can you post some sample data?

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Thanks for pointing that out.
In the comment section is also the way I would do it:

# re: The simplest way to delete duplicates and compare two result sets in SQL Server 
DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, ... ORDER BY Col1) AS recID
) AS f
WHERE recID > 2 
1/5/2009 4:57 PM | Peso

No, you're never too old to Yak'n'Roll if you're too young to die.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-09 : 11:31:26

Hello DBAintheMaking,
Here is the sample data with three columns id, rmtitle, received date

ID - RMTITLE - Receiveddate
----------------------------------------------------------------------
2794 - RE: NNHP RE: Complinace with FIDIC requirements - 2010-04-08 21:47:00.000
2795 - RE: Complinace with FIDIC requirements - 2010-04-08 21:52:36.000
2796 - Re: Complinace with FIDIC requirements - 2010-04-09 06:48:02.000
2797 - Re: Complinace with FIDIC requirements - 2010-04-09 06:48:02.000
2798 - Re: Complinace with FIDIC requirements - 2010-04-09 07:07:59.000
2799 - Re: Complinace with FIDIC requirements - 2010-04-09 07:07:59.000
2800 - Re: Complinace with FIDIC requirements - 2010-04-09 07:28:42.000
2801 - Re: Complinace with FIDIC requirements - 2010-04-09 07:28:42.000
2802 - Re: Complinace with FIDIC requirements - 2010-04-09 07:48:14.000
2803 - Re: Complinace with FIDIC requirements - 2010-04-09 07:48:14.000
2804 - RE: NNHP - DB Tender Package - 2010-04-09 08:10:38.000
2805 - RE: NNHP - DB Tender Package - 2010-04-09 08:10:38.000
2806 - RE: NNHP - DB Tender Package - 2010-04-09 08:11:53.000
2807 - RE: NNHP - DB Tender Package - 2010-04-09 08:10:38.000
2808 - RE: NNHP - DB Tender Package - 2010-04-09 08:11:53.000
2809 - NNHP RFP package 1 questions April 8 - 2010-04-09 08:56:10.000
2810 - NNHP RFP package 1 questions April 8 - 2010-04-09 08:56:10.000
2811 - RE: interim draft - CM exec summary - 2010-04-09 08:56:12.000
2812 - NNHP RFP package 1 questions April 8 - 2010-04-09 08:56:10.000
2813 - RE: interim draft - CM exec summary - 2010-04-09 08:56:12.000
2814 - RE: Scope of Work - 2010-04-09 09:05:34.000
2815 - NNHP RFP package 1 questions April 8 - 2010-04-09 08:56:10.000
2816 - RE: interim draft - CM exec summary - 2010-04-09 08:56:12.000
2817 - RE: Scope of Work - 2010-04-09 09:05:34.000
2818 - FW: NNHP PROJECT MEMBERS - 2010-04-09 09:36:32.000
2819 - RE: NNHP PROJECT MEMBERS - 2010-04-09 11:16:14.000

Please let me know. Thanks.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 12:05:57

quote:
Originally posted by webfred
Thanks for pointing that out.

No worries.

quote:

In the comment section is also the way I would do it:

# re: The simplest way to delete duplicates and compare two result sets in SQL Server 
DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, ... ORDER BY Col1) AS recID
) AS f
WHERE recID > 2

I had to do a bit of work to that before I could get it to work. Here's what I came up with
[code]CREATE TABLE #tmp (
ID INT,
RMTITLE VARCHAR(100),
Receiveddate DATETIME
)
GO

INSERT INTO #tmp
SELECT 2794, 'RE: NNHP RE: Complinace with FIDIC requirements', '2010-04-08 21:47:00.000'
UNION ALL SELECT 2795, 'RE: Complinace with FIDIC requirements', '2010-04-08 21:52:36.000'
UNION ALL SELECT 2796, 'Re: Complinace with FIDIC requirements', '2010-04-09 06:48:02.000'
UNION ALL SELECT 2797, 'Re: Complinace with FIDIC requirements', '2010-04-09 06:48:02.000'
UNION ALL SELECT 2798, 'Re: Complinace with FIDIC requirements', '2010-04-09 07:07:59.000'
UNION ALL SELECT 2799, 'Re: Complinace with FIDIC requirements', '2010-04-09 07:07:59.000'
UNION ALL SELECT 2800, 'Re: Complinace with FIDIC requirements', '2010-04-09 07:28:42.000'
UNION ALL SELECT 2801, 'Re: Complinace with FIDIC requirements', '2010-04-09 07:28:42.000'
UNION ALL SELECT 2802, 'Re: Complinace with FIDIC requirements', '2010-04-09 07:48:14.000'
UNION ALL SELECT 2803, 'Re: Complinace with FIDIC requirements', '2010-04-09 07:48:14.000'
UNION ALL SELECT 2804, 'RE: NNHP - DB Tender Package', '2010-04-09 08:10:38.000'
UNION ALL SELECT 2805, 'RE: NNHP - DB Tender Package', '2010-04-09 08:10:38.000'
UNION ALL SELECT 2806, 'RE: NNHP - DB Tender Package', '2010-04-09 08:11:53.000'
UNION ALL SELECT 2807, 'RE: NNHP - DB Tender Package', '2010-04-09 08:10:38.000'
UNION ALL SELECT 2808, 'RE: NNHP - DB Tender Package', '2010-04-09 08:11:53.000'
UNION ALL SELECT 2809, 'NNHP RFP package 1 questions April 8', '2010-04-09 08:56:10.000'
UNION ALL SELECT 2810, 'NNHP RFP package 1 questions April 8', '2010-04-09 08:56:10.000'
UNION ALL SELECT 2811, 'RE: interim draft - CM exec summary', '2010-04-09 08:56:12.000'
UNION ALL SELECT 2812, 'NNHP RFP package 1 questions April 8', '2010-04-09 08:56:10.000'
UNION ALL SELECT 2813, 'RE: interim draft - CM exec summary', '2010-04-09 08:56:12.000'
UNION ALL SELECT 2814, 'RE: Scope of Work', '2010-04-09 09:05:34.000'
UNION ALL SELECT 2815, 'NNHP RFP package 1 questions April 8', '2010-04-09 08:56:10.000'
UNION ALL SELECT 2816, 'RE: interim draft - CM exec summary', '2010-04-09 08:56:12.000'
UNION ALL SELECT 2817, 'RE: Scope of Work', '2010-04-09 09:05:34.000'
UNION ALL SELECT 2818, 'FW: NNHP PROJECT MEMBERS', '2010-04-09 09:36:32.000'
UNION ALL SELECT 2819, 'RE: NNHP PROJECT MEMBERS', '2010-04-09 11:16:14.000'
GO

DELETE
FROM #tmp
WHERE ID IN (
SELECT ID FROM(
SELECT ID, ROW_NUMBER() OVER (PARTITION BY RMTITLE, Receiveddate ORDER BY ID) AS recID
FROM #tmp) z
WHERE recID > 1 )

SELECT * FROM #tmp
GO

DROP TABLE #tmp
GO

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2010-04-09 : 12:07:56

It worked Excellent.
Really Appreceate DBA, thank you very much for the help.

Thaaanks.
Cplusplus

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-09 : 12:51:19

No worries c increment. :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-12 : 02:17:48


delete t from
(
SELECT ID, ROW_NUMBER() OVER (PARTITION BY RMTITLE, Receiveddate  ORDER BY ID) AS recID
		FROM #tmp
) as t
where recID>1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources