Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Delete Records

Prabhu_7
Frequent Advisor

Delete Records

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
6 REPLIES
Brian Crabtree
Honored Contributor

Re: Delete Records

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

Thanks,

Brian
Prabhu_7
Frequent Advisor

Re: Delete Records

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

Re: Delete Records

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

Re: Delete Records

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

Re: Delete Records

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

Re: Delete Records

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.