Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
How to delete duplicate rows where there is no Primary key

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

New User


Joined: 07 Jun 2004
Posts: 20

PostPosted: Thu Jun 10, 2004 9:54 am    Post subject: How to delete duplicate rows where there is no Primary key
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    Post subject: Re: db2
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    Post subject: Here is one way...
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    Post subject:
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    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 Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm
No new posts column with count of rows within dist... ronald wouterson DB2 4 Sun Sep 17, 2017 9:48 pm
No new posts Convert rows and column into JSON for... Dinesh Manivannan DB2 2 Sun Sep 03, 2017 6:50 pm
No new posts Skip file from delete pema_yozer All Other Mainframe Topics 3 Tue Aug 29, 2017 3:19 pm
No new posts Delete IMS remote transaction when MO... Sam Singh IMS DB/DC 0 Wed Aug 23, 2017 9:12 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us