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
 

 

How 2 delete dup rows with single qury in DB2?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
Help-Me-Out

New User


Joined: 09 Dec 2006
Posts: 56
Location: Pune

PostPosted: Tue Dec 18, 2007 12:46 pm    Post subject: How 2 delete dup rows with single qury in DB2?
Reply with quote

Hi,

Can any one has idea about how to delete all duplicate rows from table with single query?

e.g. table contains data like

CUST_ID NAME DEPT

123 ABC A1
123 ABC B1
123 ABC C1
124 ABC A1
125 ABC A1
126 ABC A1
126 ABC B1

Output should be

CUST_ID NAME DEPT

124 ABC A1
125 ABC A1


Thanks
Back to top
View user's profile Send private message

murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1439
Location: Bangalore,India

PostPosted: Tue Dec 18, 2007 2:30 pm    Post subject:
Reply with quote

Sandy,

One of the forum's topic -

http://ibmmainframes.com/viewtopic.php?t=3982&highlight=delete
Back to top
View user's profile Send private message
padmaambati

New User


Joined: 14 Nov 2007
Posts: 1
Location: hyderabad

PostPosted: Wed Dec 19, 2007 8:14 pm    Post subject:
Reply with quote

i think we can use many operationsbut u had asked only for single query,in my point of view the better one is "select count(distinctcust_id)from tablename.the other querries are delete,join.union,group by
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Dec 19, 2007 9:58 pm    Post subject:
Reply with quote

Hello Paddu and welcome to the forums,

Quote:
in my point of view the better one is "select count(distinctcust_id)from tablename
How does this relate to the original request to delete duplicates?

Quote:
the other querries are delete,join.union,group by
How will we use this information?
Back to top
View user's profile Send private message
Help-Me-Out

New User


Joined: 09 Dec 2006
Posts: 56
Location: Pune

PostPosted: Fri Dec 28, 2007 2:36 pm    Post subject: Reply to: How 2 delete dup rows with single qury in DB2?
Reply with quote

Hi correcting my previous question..

Can any one has idea about how to delete all duplicate rows from table with single query?

e.g. table contains data like

CUST_ID NAME DEPT

123 ABC A1
123 ABC A1
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1
126 ABC A1

Output should be

CUST_ID NAME DEPT
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1

I went thru the previous post but the answer is not what the above output is..
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri Dec 28, 2007 2:47 pm    Post subject:
Reply with quote

Hi Sandy,

Using Distinct verb in your select query you can achive your results.
Back to top
View user's profile Send private message
Help-Me-Out

New User


Joined: 09 Dec 2006
Posts: 56
Location: Pune

PostPosted: Fri Dec 28, 2007 3:33 pm    Post subject: Reply to: How 2 delete dup rows with single qury in DB2?
Reply with quote

Can you give me the query... mine is nt working..
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri Dec 28, 2007 4:00 pm    Post subject:
Reply with quote

Code:

SELECT DISTINCT CUST_ID FROM TABLE-NAME


Use the respective coloumn name in place of CUST_ID.
Back to top
View user's profile Send private message
arivazhagan_k

New User


Joined: 05 Dec 2007
Posts: 57
Location: chennai

PostPosted: Fri Dec 28, 2007 4:47 pm    Post subject:
Reply with quote

ksk ,

what about for this requirement ?

Code:
 


INPUT DATA       

             CUST_ID         NAME         DEPT
           
             123             ABC            A1
             123             ABC            A1
             123             ABC            B1  --> see 
             123             ABC            C1  --> see
             123             ABC            A1
             124             ABC            A1
             125             ABC            A1
             126             ABC            A1
             126             ABC            A1

OUTPUT     

             123             ABC            A1
             123             ABC            B1
             123             ABC            C1
             124             ABC            A1
             125             ABC            A1
             126             ABC            A1
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri Dec 28, 2007 5:10 pm    Post subject:
Reply with quote

arivazhagan.k,

Wirte 2 DISTINCT queries for CUST_ID and DEPT and use UNION so that if any duplicates exists, will get eliminated.

For your above data, for CUST_ID, you will get

123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1

for DEPT,

123 ABC A1
123 ABC B1
123 ABC C1

If you do UNION on these duplicate record 123ABCA1 will get deleted and you will get the unique results.

Please correct me if I am wrong in my solution.

Regards,
KSK
Back to top
View user's profile Send private message
arivazhagan_k

New User


Joined: 05 Dec 2007
Posts: 57
Location: chennai

PostPosted: Fri Dec 28, 2007 5:17 pm    Post subject:
Reply with quote

How about this query ?

Code:
SELECT    CUST_ID,NAME,DEPT
FROM      CUSTOMER
GROUP BY  CUST_ID,NAME,DEPT
Back to top
View user's profile Send private message
die7nadal

Active User


Joined: 23 Mar 2005
Posts: 156

PostPosted: Fri Dec 28, 2007 10:48 pm    Post subject:
Reply with quote

The easiest way I can think of is, unload the Data to a flat file and run it through a sort job to eliminate the dups and then load it into the table again. It can get very complex in a DB2 query.
Back to top
View user's profile Send private message
Help-Me-Out

New User


Joined: 09 Dec 2006
Posts: 56
Location: Pune

PostPosted: Wed Jan 02, 2008 2:15 pm    Post subject: Reply to: How 2 delete dup rows with single qury in DB2?
Reply with quote

icon_cry.gif sorry, but no one is giving me the reply how to delete the duplicate rows.... everyone is concetrating on the selecting the rows icon_surprised.gif
i wanna delete the duplicate rows..... icon_question.gif

plz help me
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Wed Jan 02, 2008 2:41 pm    Post subject: Reply to: How 2 delete dup rows with single qury in DB2?
Reply with quote

Quote:
i wanna delete the duplicate rows


before deleting them You must find them...

if this is not homework why insist on "single query"

when sweeping a whole table
from a point of view of performance, space utilization recovery
the BEST thing to do is ..

unload, process, reload

finding out how many duplicates You have is the first thing to do
in order to decide how to carry on any further updates
Back to top
View user's profile Send private message
abhishekmdwivedi

New User


Joined: 22 Aug 2006
Posts: 95
Location: india

PostPosted: Wed Jan 02, 2008 3:33 pm    Post subject: Reply to: How 2 delete dup rows with single qury in DB2?
Reply with quote

Quote:

Hi correcting my previous question..

Can any one has idea about how to delete all duplicate rows from table with single query?

e.g. table contains data like

CUST_ID NAME DEPT

123 ABC A1
123 ABC A1
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1
126 ABC A1

Output should be

CUST_ID NAME DEPT
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1


As per the above requirement , below query may do the trick.

DELETE FROM table_name where
CUST_ID in
(SELECT CUST_ID from table_name
GROUP BY CUST_ID HAVING COUNT(*) > 1) ;
Back to top
View user's profile Send private message
die7nadal

Active User


Joined: 23 Mar 2005
Posts: 156

PostPosted: Fri Jan 04, 2008 4:51 am    Post subject: Re: Reply to: How 2 delete dup rows with single qury in DB2?
Reply with quote

abhishekmdwivedi wrote:
Quote:

Hi correcting my previous question..

Can any one has idea about how to delete all duplicate rows from table with single query?

e.g. table contains data like

CUST_ID NAME DEPT

123 ABC A1
123 ABC A1
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1
126 ABC A1

Output should be

CUST_ID NAME DEPT
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1


As per the above requirement , below query may do the trick.

DELETE FROM table_name where
CUST_ID in
(SELECT CUST_ID from table_name
GROUP BY CUST_ID HAVING COUNT(*) > 1) ;


This query will delete all the dups, but the requirement was to keep 1 record among the duplicate records. So ineffect the below records will not be present in the output.

123 ABC A1
126 ABC A1
Back to top
View user's profile Send private message
mastrahoyar

New User


Joined: 03 Mar 2007
Posts: 17
Location: hyd

PostPosted: Wed Aug 06, 2008 10:53 am    Post subject: how to delete duplicate rows
Reply with quote

Hi abhishek.

If table contains data like

CUST_ID NAME DEPT

123 ABC A1
123 EFG A1
123 ABC A1
124 ABC A1
125 ABC A1
126 ABC A1
126 ABC A1

Output should be

CUST_ID NAME DEPT
123 ABC A1
123 EFG A1
124 ABC A1
125 ABC A1
126 ABC A1

Now can you please tell me the query which delete duplicate rows..
Back to top
View user's profile Send private message
samuel_Inba

New User


Joined: 03 Jan 2008
Posts: 53
Location: Chennai

PostPosted: Wed Aug 06, 2008 11:54 am    Post subject:
Reply with quote

hi,
I have a doubt pls clarify me,


DELETE FROM table_name where
CUST_ID in
(SELECT CUST_ID from table_name
GROUP BY CUST_ID HAVING COUNT(*) > 1) ;

The inner query will return all the CUST_ID which has rows more than one,
and the outer query will delete all the rows which was returned by the inner query.
but as per the above requirement,

One occurance should be kept... not all of them to be deleted.
e.g:
1
1
1
2
2
then the output shud be
1
2
...

pls let me know if i understood incorrectly.
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 -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


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