Stored proc to delete double records

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)

Stored proc to delete double records

Author

Topic

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-09-25 : 23:06:37

Hi experts,

I would like to ask how to create a stored proc. that will delete double records?

www.itchytech.com

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2008-09-25 : 23:15:45

Can you be more specific? What do you meant by "delete double record"
Thanks
Saurabh

d3ng
Yak Posting Veteran

83 Posts

Posted - 2008-09-25 : 23:24:29

for ex.

We have a table customer with the following fields:

pkeyno int (primary key)
dcno int (unique for each customer)
gntrexpiry smalldatetime
billcode char(10)
custclass varchar(15)
...

sample output

pkeyno dcno gntrexpiry billcode
1 1111 '09/26/2010' '000-1000'
2 1111 '09/26/2010' '000-1000'
3 2222 '09/26/2010' '000-1001'
4 2222 '09/26/2010' '000-1001'
5 3333 '09/26/2010' '000-1002'

I need to delete the second record and retain the original.

www.itchytech.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:24:50

[code]CREATE PROC DeleteDuplicates
AS

DELETE t
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY dcno, gntrexpiry, billcode ORDER BY pkeyno) AS Seq,*
FROM YourTable)t
WHERE t.Seq>1
GO[/code]

or

[code]CREATE PROC DeleteDuplicates
AS

DELETE t
FROM YourTable t
LEFT JOIN (SELECT dcno, gntrexpiry, billcode,MIN(pkeyno) AS MinRec
FROM YourTable
GROUP BY dcno, gntrexpiry, billcode)t1
ON t1.dcno=t.dcno
AND t1.gntrexpiry=t.gntrexpiry
AND t1.billcode=t.billcode
AND t1.MinRec=t.pkeyno
WHERE t1.dcno IS NULL
GO[/code]

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2008-09-26 : 01:27:09

first get the duplicates
then find the max id of the duplicate
then delete the max

You may need a loop to run multiple times so the 3rd, 4th... duplicates are deleted untill you have distinct values.

delete from customer x
join
(
select max(pkeyno), dcno, gntrexpiry, billcode
from
customer c
join (select dcno, gntrexpiry, billcode from customer group by dcno, gntrexpiry, billcode having count(*) > 1) d
on c.dcno = d.dcno and c.gntrexpiry = d.gntrexpiry and c.billcode = d.billcode
group by dcno, gntrexpiry, billcode ) y
on x.pkeyno = y.pkeyno

You can do anything at www.zombo.com

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2008-09-26 : 01:28:32

I like visakh16 solution

You can do anything at www.zombo.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:35:56

quote:
Originally posted by clarkbaker1964

first get the duplicates
then find the max id of the duplicate
then delete the max

You may need a loop to run multiple times so the 3rd, 4th... duplicates are deleted untill you have distinct values.

delete from customer x
join
(
select max(pkeyno), dcno, gntrexpiry, billcode
from
customer c
join (select dcno, gntrexpiry, billcode from customer group by dcno, gntrexpiry, billcode having count(*) > 1) d
on c.dcno = d.dcno and c.gntrexpiry = d.gntrexpiry and c.billcode = d.billcode
group by dcno, gntrexpiry, billcode ) y
on x.pkeyno = y.pkeyno

You can do anything at www.zombo.com

This will work correctly only if you've max of 2 occurances per value. If there are any case of value occuring more than 2 times, it will delete only final occurance and other two will remain.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-26 : 01:36:21