IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Deleting Duplicates from DB2 Tables


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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: 677
Location: NJ

PostPosted: Fri Nov 18, 2005 2:41 pm
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
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
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
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
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
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

Global Moderator


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

PostPosted: Wed Jun 06, 2007 9:13 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Deleting a Tape file JCL & VSAM 14
No new posts CA7 deleting files in steps after job... CA Products 4
No new posts Need to fetch data from so many DB2 t... DB2 9
No new posts How to remove block of duplicates DFSORT/ICETOOL 8
This topic is locked: you cannot edit posts or make replies. Compare files with duplicates in one ... DFSORT/ICETOOL 11
Search our Forums:

Back to Top