- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Delete Records
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-08-2003 09:22 AM
тАО09-08-2003 09:22 AM
Delete Records
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-08-2003 12:10 PM
тАО09-08-2003 12:10 PM
Re: Delete Records
Thanks,
Brian
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-08-2003 12:25 PM
тАО09-08-2003 12:25 PM
Re: Delete Records
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-08-2003 01:03 PM
тАО09-08-2003 01:03 PM
Re: Delete Records
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-12-2003 07:13 AM
тАО09-12-2003 07:13 AM
Re: Delete Records
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 )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-12-2003 01:00 PM
тАО09-12-2003 01:00 PM
Re: Delete Records
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО09-15-2003 02:32 AM
тАО09-15-2003 02:32 AM
Re: Delete Records
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.