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

How to delete duplicate rows where there is no Primary key


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
srinivas.u

New User


Joined: 07 Jun 2004
Posts: 19

PostPosted: Thu Jun 10, 2004 9:54 am
Reply with quote

hi

i have 100 record in table but i am not declared in primary key
how to delete duplicate rows in table plz send to mail
Back to top
View user's profile Send private message
jz1b0c

Active User


Joined: 25 Jan 2004
Posts: 160
Location: Toronto, Canada

PostPosted: Tue Nov 16, 2004 12:01 am
Reply with quote

srinivas.u wrote:
hi

i have 100 record in table but i am not declared in primary key
how to delete duplicate rows in table plz send to mail



based on some key or clumns you must be identifying them as duplicates
did you identify those columns?
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Thu Dec 16, 2004 5:05 pm
Reply with quote

Hi Srini,

You can delete all duplicate records by using the following query... but as I think it will delete all duplicates inclusing original... check out..

delete from table a where 1!=(select count(*) from table b where b.col1=a.col1 and b.col2=a.col2 and b.col3=a.col3 and ......)

Bye,
Reddy.
Back to top
View user's profile Send private message
Hames

New User


Joined: 03 Oct 2005
Posts: 49

PostPosted: Thu Nov 24, 2005 10:52 am
Reply with quote

Hi Srinivas,

Code:


DELETE                                                         
FROM TABLE A                                                     
WHERE (A.COL1,A.COL2,A.COL3)
IN (SELECT B.COL1,B.COL2,B.COL3   
                         FROM TABLE B                       
                                  WHERE A.COL1 = B.COL1             
                                    AND A.COL2 = B.COL2             
                                    AND A.COL3 = B.COL3             
                                  GROUP BY B.COL1, B.COL2, B.COL3   
                                 HAVING COUNT(*) > 1);
 


You can try this query also.
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 DELETE SPUFI DB2 1
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts DSNTIAUL driven delete IBM Tools 0
No new posts Exclude rows with > than x occurre... DFSORT/ICETOOL 6
Search our Forums:

Back to Top