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
 
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 MXG - Processing Compressed DB2 SMF r... vasanthz All Other Mainframe Topics 2 Thu May 10, 2018 12:47 am
No new posts Duplicate PARM on OUTFIL sancraig16 SYNCSORT 11 Thu Apr 05, 2018 10:25 pm
No new posts Extract the records with a PD field's... sudhakar84 DFSORT/ICETOOL 11 Mon Apr 02, 2018 7:26 pm
No new posts Merge 2 records sancraig16 SYNCSORT 19 Tue Mar 27, 2018 8:17 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