delete duplicate records using a subquery in from clause

l.nethaji · Posted: Thu Nov 25, 2010 2:13 pm

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

GuyC · Posted: Thu Nov 25, 2010 3:05 pm

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.

l.nethaji · Posted: Thu Nov 25, 2010 3:42 pm

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.

GuyC · Posted: Thu Nov 25, 2010 3:49 pm

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.

GuyC · Posted: Thu Nov 25, 2010 4:12 pm

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

l.nethaji · Posted: Thu Nov 25, 2010 5:28 pm

Hi,

Thanks u Guyc ,

Cheers,
Nethaji.L