Deleting duplicate records from a table or object

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

General SQL Server Forums

New to SQL Server Programming

Deleting duplicate records from a table or object

Author

Topic

sundayose
Starting Member

21 Posts

Posted - 2012-10-25 : 11:01:42

I have a table named table1. I need to write a query to delete rows that have the column voucher with duplicates remaining with one row of the duplicates with the smallest ID.
For example, voucher B002 has 3 duplicates at ID 2, 4 and 7 . I need to delete all the duplicates and remaining the the voucher with the smallest ID.
This table has more vouchers than displayed. I just took a snapshot.
Table1
ID Voucher CreatedBy
1 B001 Moses
2 B002 James
3 B001 James
4 B002 Moses
5 B003 Moses
6 B004 James
7 B002 James
8 B004 Moses
9 B005 Moses
10 B006 Moses
11 B001 James
Contributions are highly welcome.
Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 11:09:42

[code]
DELETE t
--SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Voucher ORDER BY ID) AS Seq
FROM Table1
)t
WHERE Seq>1

or this

DELETE t
--SELECT t.*
FROM Table1 t
INNER JOIN (SELECT Voucher,MIN(ID) AS MinID
FROM Table1
GROUP BY Voucher)tmp
ON tmp.Voucher = t.Voucher
AND tmp.MinID = t.ID
[/code]

first run with delete comented and select uncommented and once you're happy put in deletes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sundayose
Starting Member

21 Posts

Posted - 2012-10-25 : 11:28:17

Hi Visak,

Thank you for the quick response.

I am requesting for some explanation of the query if possible add some comments.
What does t and tmp stand for in the code?

Thanx.

quote:
Originally posted by visakh16

DELETE t
--SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY Voucher ORDER BY ID) AS Seq
FROM Table1
)t
WHERE Seq>1

or this

DELETE t
--SELECT t.*
FROM Table1 t
INNER JOIN (SELECT Voucher,MIN(ID) AS MinID
FROM Table1
GROUP BY Voucher)tmp
ON tmp.Voucher = t.Voucher
AND tmp.MinID = t.ID

first run with delete comented and select uncommented and once you're happy put in deletes

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 11:36:09

they're table aliases ie short names given for the derived tables created on the fly using inner queries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-25 : 11:37:29

sorry there's a slight issue with second query it should be the below


DELETE t
--SELECT t.*
FROM Table1 t
LEFT JOIN (SELECT Voucher,MIN(ID) AS MinID
            FROM Table1
            GROUP BY Voucher)tmp
ON tmp.Voucher = t.Voucher
AND tmp.MinID = t.ID
WHERE tmp.Voucher IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sundayose
Starting Member

21 Posts

Posted - 2012-10-28 : 04:13:29

Thanks Visak.
It worked perfectly.

quote:
Originally posted by visakh16

sorry there's a slight issue with second query it should be the below

DELETE t
--SELECT t.*
FROM Table1 t
LEFT JOIN (SELECT Voucher,MIN(ID) AS MinID
FROM Table1
GROUP BY Voucher)tmp
ON tmp.Voucher = t.Voucher
AND tmp.MinID = t.ID
WHERE tmp.Voucher IS NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 09:37:15

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources