topic Delete Records in Operating System - HP-UX

Delete Records

Prabhu_7 — Mon, 08 Sep 2003 16:22:58 GMT

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

Re: Delete Records

Brian Crabtree — Mon, 08 Sep 2003 19:10:55 GMT

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

Thanks,

Brian

Re: Delete Records

Prabhu_7 — Mon, 08 Sep 2003 19:25:54 GMT

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

Re: Delete Records

Brian Crabtree — Mon, 08 Sep 2003 20:03:03 GMT

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

Re: Delete Records

John Jayaseelan — Fri, 12 Sep 2003 14:13:31 GMT

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 )

Re: Delete Records

Brian Crabtree — Fri, 12 Sep 2003 20:00:11 GMT

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

Sandro Schaer_1 — Mon, 15 Sep 2003 09:32:38 GMT

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.