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

Query to remove the duplicate rows from the table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
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 Sortjoin and Search for a String and ... DFSORT/ICETOOL 1
No new posts Pulling a fixed number of records fro... DB2 2
Search our Forums:

Back to Top