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
 

 

Query to remove the duplicate rows from the table

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

New User


Joined: 27 Feb 2004
Posts: 4

PostPosted: Wed May 05, 2004 8:24 am    Post subject: Query to remove the duplicate rows from the table
Reply with quote

hi folks,

Pls. help me in writing queries. How to remove the duplicate rows from the
table and also to delete the duplicate rows from the table.
Back to top
View user's profile Send private message

mvs_butta

New User


Joined: 23 Dec 2003
Posts: 13

PostPosted: Wed May 05, 2004 6:20 pm    Post subject:
Reply with quote

Hop this will help u

table temp with col as id and having values

2
3
4
3
2
5
6

delete from temp a
where a.id in (select b.id from temp b
where a.id = b.id group by b.id having count(*) > 1)

will give you

4
5
6
Back to top
View user's profile Send private message
Gurucharan

New User


Joined: 27 Feb 2004
Posts: 4

PostPosted: Fri May 14, 2004 8:20 am    Post subject: how to remove the duplicates from the table
Reply with quote

Hi folks,

I need the first occurence of the duplicate.

2
3
2
2
4
3
5
6

i need output like this:

2
3
4
5
6
Back to top
View user's profile Send private message
ConnoisseuR

New User


Joined: 06 Aug 2004
Posts: 8
Location: india

PostPosted: Mon Nov 01, 2004 9:36 am    Post subject:
Reply with quote

you can use DISTINCT key word to eliminate duplicate records.all yu'll get will just be the first occurance of the duplicate record.
dp i make myself clear.
Back to top
View user's profile Send private message
karthi_ind

Active User


Joined: 24 Feb 2004
Posts: 131
Location: Chennai

PostPosted: Fri Nov 05, 2004 1:20 pm    Post subject:
Reply with quote

hi

the answer for the abve ques?

delete from temp whre id in
Code:
( select id from temp
 group by id
 having count(*) >1)
Back to top
View user's profile Send private message
ksivapradeep

New User


Joined: 30 Jul 2004
Posts: 95

PostPosted: Wed Nov 10, 2004 4:02 pm    Post subject:
Reply with quote

hi karthi,

ur query giving -118 error(the insert, update, insert,delete referensing same table in from clause).i tried with range variables but i am not getting plz check it out once again.

regards,
siva pradeep
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 Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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