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
 
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 How to force a select query to abend neo4u DB2 5 Mon Apr 23, 2018 1:36 pm
No new posts Duplicate PARM on OUTFIL sancraig16 SYNCSORT 11 Thu Apr 05, 2018 10:25 pm
No new posts Searching a table for the field name? socker_dad COBOL Programming 8 Sat Mar 31, 2018 2:57 am
No new posts Need inputs on DB2 tablespace/table r... ashek15 DB2 3 Sat Mar 24, 2018 11:49 pm
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am

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