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
 

 

delete duplicate records using a subquery in from clause

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

New User


Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu

PostPosted: Thu Nov 25, 2010 2:13 pm    Post subject: delete duplicate records using a subquery in from clause
Reply with quote

Hi,

I have a table t1 with col A int and B int.

A B
1 2
2 3
1 2

In the table we can see duplicate records in Table t1.

I am writing a query as below

delete from ( select a,b,count(*) from t1 group by a,b having count(*) >1).

the subquery runs fine if i run it separately. But it give me an error when used as a subquery.

Please help me to solve this issue.

Regards,
L.Nethaji
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 25, 2010 3:05 pm    Post subject:
Reply with quote

Your subselect creates a "work table" , you can't delete from a work_table.

you 'll need something like
delete from T1 where ...
and if you delete all rows having count(*) > 1 you will be deleting both rows.

If you want to delete with a "Where..." you'll need something that uniquely defines the row you want to delete, like timestamp.
There are other ways like unloading (without duplicates)/reloading or copying (without duplicates) to a private table /empty the original/copy back.
or a program with a cursor and a delete where current of if equal to the previous fetch.
Back to top
View user's profile Send private message
l.nethaji

New User


Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu

PostPosted: Thu Nov 25, 2010 3:42 pm    Post subject: Reply to: delete duplicate records using a subquery in from
Reply with quote

Hi Guyc,

Thanks for the reply ,

I hope work table u mentioned is a table temporarily created .

I tried the below query for the same table t1

" Delete from (select b from test.t1 where a=1) "

DB2 creates a temporary or work table for the subquery but it rightly deleted the records having a = 1 .

Please let me know what the process wrongly happening behind when we use group by and count(*).

I have tried it and its working fine if we use current of using cursor.

I am trying to delete duplicates using subquery without using cursors.

Thanks in advance,
L.Nethaji.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 25, 2010 3:49 pm    Post subject:
Reply with quote

the rules for a subquery are like the rules for a view :
some are updateable, some are not.
subselects with group by are not updateable.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 25, 2010 4:12 pm    Post subject:
Reply with quote

there is no subselect that will allow you to delete only the duplicate , if you don't have any other column(-combination) to uniquely define the row
Back to top
View user's profile Send private message
l.nethaji

New User


Joined: 16 Mar 2008
Posts: 90
Location: tamil nadu

PostPosted: Thu Nov 25, 2010 5:28 pm    Post subject: Reply to: delete duplicate records using a subquery in from
Reply with quote

Hi,

Thanks u Guyc ,

Cheers,
Nethaji.L
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 Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am


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