Delete Records

[Prabhu_7]

For reference...File Attached

For the combination Loan_Lease||Prod_short||act_num
i have 2 customers. I want delete either one of the the customer.

How the delete statement should be ?

Thanks

[Brian Crabtree]

Your file is unreadable by word. Can you upload it with notepad instead?

Thanks,

Brian

[Prabhu_7]

For combination of Loan_Lease||Prod_short||act_num
i have two or more customers.
I want to keep only one customer and delete
others for this combination.


Select cus_num, Loan_Lease,Prod_short,act_num
from bts_caf_file where Loan_Lease||Prod_short||act_num in (
select Loan_Lease||Prod_short||act_num
from bts_caf_file where status is null
group by Loan_Lease||Prod_short||act_num
having count(*) > 1
)
order by Loan_Lease||Prod_short||act_num

Sample Data

Cusnum Loan_Lease Prodshort Act_Num

00001 AM CRD 1000
00002 AM CRD 1000

00003 AM CRD 2000
00004 AM CRD 2000
00008 AM CRD 2000
00005 AM CRD 2000

00006 AM CRD 3000
00009 AM CRD 3000
00010 AM CRD 3000

Output should be

Cusnum Loan_Lease Prodshort Act_Num

00001 AM CRD 1000
00003 AM CRD 2000
00006 AM CRD 3000


Thanks

[Brian Crabtree]

Select cus_num, Loan_Lease,Prod_short,act_num
from bts_caf_file where Loan_Lease||Prod_short||act_num in (
select Loan_Lease||Prod_short||act_num
from bts_caf_file where status is null
group by Loan_Lease||Prod_short||act_num
having count(*) > 1
)
and cus_num in (select min(cus_num) from bts_caf_file group by loan_lease||prod_short||act_num)
order by Loan_Lease||Prod_short||act_num

Hope this helps,

Thanks,

Brian

[John Jayaseelan]

The following is modifed version of brian's SQL that will delete the duplicates


delete from bts_caf_file where
cus_num||Loan_Lease||Prod_short||act_num not in
(Select cus_num||Loan_Lease||Prod_short||act_num from bts_caf_file where Loan_Lease||Prod_short||act_num in (
select Loan_Lease||Prod_short||act_num
from bts_caf_file where status is null
group by Loan_Lease||Prod_short||act_num
having count(*) > 1
)
and cus_num in (select min(cus_num) from bts_caf_file group by loan_lease||prod_short||act_num)
order by Loan_Lease||Prod_short||act_num )

[Brian Crabtree]

John,

This is the best duplicate record delete statement I have ever used. I can't take credit for it, I found it on Metalink a long time ago:

delete from contact
where rowid in
(select rowid from contact
minus
select max(rowid) from contact
group by email)

You have to change the "group by" statement and the tables used, but this is clean, simple, and works really well.

Brian

[Sandro Schaer_1]

looks like you want to keep the first row for each customer. so why not using the 'min' function?


delete from bts_caf_file where
cus_num||Loan_Lease||Prod_short||act_num not in
(Select min(cus_num||Loan_Lease||Prod_short||act_num) from bts_caf_file group by cus_num||Loan_Lease||Prod_short||act_num)


I guess this should work.