Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Deleting Duplicates from DB2 Tables

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
annanagarlt

New User


Joined: 31 Oct 2003
Posts: 5

PostPosted: Tue Dec 09, 2003 8:22 pm    Post subject: Deleting Duplicates from DB2 Tables
Reply with quote

Dear All,
I would like to DELETE DUPLICATE Records from a DB2 TABLE

Note : Table doesnt have a Primary Key Definition.

I dont want to use a new table

Pls I beg to explain the QUERY in a more Proper way...

Looking forward in exploring this further...


Cheers

Team AnnaNagar LT
Back to top
View user's profile Send private message

anuradha

Active User


Joined: 06 Jan 2004
Posts: 247
Location: Hyderabad

PostPosted: Wed Jan 07, 2004 9:43 pm    Post subject:
Reply with quote

Hi,

I wonder if I clarify your doubt.your question to me is still not clear icon_confused.gif .But I am trying to answer.To delete the duplicate rows first you need to find out them.suppose your table say samp contains 3 columns A,B,C then u can find the duplicates by the following query

select A, B, C, count(*) from samp
group by A,B,C
having count(*) > 1


Then after finding this I think you can delete them easily.

OR
IF YOU CAN CREATE A TEMP TABLE U CAN FOLLOW THIS WAY:

CREATE TEMPORARY TABLE samp_temp AS SELECT DISTINCT * FROM samp


Then say:
delete from samp

so now samp is empty

now load all rows(which dont have any duplicates) into the samp table.


THANKS AND REGARDS
ANURADHA
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Fri Nov 18, 2005 2:41 pm    Post subject: Query
Reply with quote

hi frnd,
try to post DB2 query in the DB2 section. and according to ur query without the help of another table how can u store the data??? Actually whats ur requirement?? So any way what anuratha said is clear!! but kindly post ur req clearly so that all can understand..
Back to top
View user's profile Send private message
Hames

New User


Joined: 03 Oct 2005
Posts: 49

PostPosted: Mon Nov 21, 2005 4:44 pm    Post subject:
Reply with quote

Hi,

In Oracle it is like

"delete from test a
where rowid <> ( select max(rowid)
from test b
where a.sno = b.sno
and a.sname = b.sname )"
.

In oracle using rowid column we can delete the duplicates easily.

But in DB2 rowid column is not available. Anuradha's answer is good for less number of variables. If a table is having more than 50 columns then think about her Query It will be difficult. There will be some simpler way for this.

I will let u know the alternative soon.
Back to top
View user's profile Send private message
Sandy-is-here

New User


Joined: 16 Mar 2006
Posts: 6
Location: Bangalore

PostPosted: Mon Apr 03, 2006 2:37 pm    Post subject:
Reply with quote

Hi All,

Suppose you want to delete the duplicate records from table and keep only one record.

Let me give an example

Table Name : Employee

Empno Name Salary

1 Ramesh 10,000

2 Ramesh 10,000

3 Ramesh 10,000

4 Suneel 20,000

5 James 25,000

6 Pradhan 30,000

7 Nagaraj 12,000

Apart from the above table , need to be delete the duplicate records namely Ramesh and should come the output like below is,

OUT PUT :
Empno Name Salary
1 Ramesh 10,000

4 Suneel 20,000

5 James 25,000

6 Pradhan 30,000

7 Nagaraj 12,000


Answer :

Delete from Employee E1 where empid <> ( select Min( empid) from Employee E2 where E2. empid = E1.empid)

Note :

(1) Having a unique EmpID makes this easy.

(2) By using a self referencing sub-query and the MIN aggregate function, you can determine the lowest ID for a given NAME.

(3) By deleting all but the lowest ID, you are left with a unique set of NAMEs.

Thanks
Sandy
Back to top
View user's profile Send private message
prakashmuthaiah

New User


Joined: 14 Mar 2006
Posts: 1

PostPosted: Mon Apr 03, 2006 4:27 pm    Post subject: use this query to delete a duplicate record
Reply with quote

delete from employee where Name in (select Name from employee group by name having count(*) > 1)
Back to top
View user's profile Send private message
skkp2006

New User


Joined: 14 Jul 2006
Posts: 93
Location: Chennai,India

PostPosted: Wed May 30, 2007 4:52 pm    Post subject: Re: Deleting Duplicates from DB2 Tables
Reply with quote

How about unloading the table into a sequential file....sorting the file with the whole record length as the key and loading the data back to the table ?????

regards,
Syam
Back to top
View user's profile Send private message
Pons

New User


Joined: 25 May 2007
Posts: 61
Location: Coimbatore

PostPosted: Mon Jun 04, 2007 10:08 pm    Post subject: Re: Deleting Duplicates from DB2 Tables
Reply with quote

Hi prakashmuthaiah,

I think your query will delete not only the duplicate rows; it will delete including the original row also.

Ex: Ramesh 10,000 will totally delete form the table.
So there is no record for Ramesh. But that is not an requirement.

DELETE FROM EMPLOYEE A
WHERE 1 < (SELECT COUNT (NAME) FROM
EMPLOYEE B
WHERE A.NAME = B.NAME);

I think this may help you. If no primay key you merge all the field and use it.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1537
Location: Andromeda Galaxy

PostPosted: Wed Jun 06, 2007 9:13 am    Post subject:
Reply with quote

Hi annanagarlt,

You can run a spufi in batch by giving

select distinct(*) for your table have the output in sysrec and the LOAD control card in syspunch

Then having that sysrec and syspunch you can run a LOAD REPLACE for the same table.

You need to modify your LOAD control card before submitting the job.

Hope this helps icon_biggrin.gif


Thanks,
Prem
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Identify top 30 big tables or indexes... ashek15 DB2 0 Fri Jun 16, 2017 10:01 am
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts ERM-Diagram of SysIBM-Catalog-Tables Auryn DB2 2 Fri Feb 17, 2017 6:22 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us