All Forums Database
08 Aug 2013
Deleting duplicate rows in multiset table

Hi,
I want to delete duplicate record, by keeping only one row for such duplicate records in Multiset table.
Suppose we have 2 rows with values 1, 2, 3 and 1, 2, 3 in my multiset table then after delete i should have only one row i.e. 1, 2, 3.
select * from jedi_mvn_db.zam_multiset
where (col1,col2,col3) in
(select col1,col2,col3 from jedi_mvn_db.zam_multiset group by 1,2,3 having count(*)>1)
qualify row_number() over (partition by col1,col2,col3 order by col1,col2,col3)>1;
above select gives me correct result. But when i convert this select statement into delete statement as below, it gives error "expected something between ")" and qualify keyword.
delete from jedi_mvn_db.zam_multiset
where (col1,col2,col3) in
(select col1,col2,col3 from jedi_mvn_db.zam_multiset group by 1,2,3 having count(*)>1)
qualify row_number() over (partition by col1,col2,col3 order by col1,col2,col3)>1;
I came to know that qualify cannot be used in delete statement.
Is there any way to delete such duplicate records with out using ROWID ?
 

Mahesh Abbigeri
Harpreet Singh 101 posts Joined 10/11
08 Aug 2013

Hi,
You can create volatile set table and insert records into this volatile table. Thsi way your volatile table will not have duplicates. Then you can delete from table and insert data from volatile table to table.

15 Aug 2013

Thanks Harpreet.
But is there any way of deleting such records directly from main table without using volatile tables or any other intermediate tables.

Mahesh Abbigeri

KS42982 137 posts Joined 12/12
15 Aug 2013

If you do not want to create any intermediate table then you can add another column and can use RANK or ROW_NUMBER and then use QUALIFY to delete duplicate record.

15 Aug 2013

Hi,
As i mentioned before in my first post,
select * from jedi_mvn_db.zam_multiset
where (col1,col2,col3) in
(select col1,col2,col3 from jedi_mvn_db.zam_multiset group by 1,2,3 having count(*)>1)
qualify row_number() over (partition by col1,col2,col3 order by col1,col2,col3)>1;
 
this statement gives me correct rows which i want to delete but when i convert the select query into delete statement like below then it gives me error ""expected something between ")" and qualify keyword."..
delete from jedi_mvn_db.zam_multiset
where (col1,col2,col3) in
(select col1,col2,col3 from jedi_mvn_db.zam_multiset group by 1,2,3 having count(*)>1)
qualify row_number() over (partition by col1,col2,col3 order by col1,col2,col3)>1;
I think we cannot use Qualify in delete statement..
 
 

Mahesh Abbigeri

dnoeth 4628 posts Joined 11/04
16 Aug 2013

Hi Mahesh,
you can't do that without an intermediate table (unless you use the internal ROWID which is no longer available).
Depending on the number of duplicates you might do Harpreet's approach (huge number) or
CREATE VOLATILE SET TABLE xxx AS  (your SELECT/QUALIFY or GROUP BY all HAVING COUNT(*) > 1), DELETE FROM xxx, re-INSERT FROM xxx
 
Dieter

Dieter

vkbagare 53 posts Joined 02/10
24 Aug 2013

Do you know if the database is in ONLINE LOGGING mode (for online backups)?
 

Best,
Vinay Bagare

umitk 1 post Joined 09/13
05 Sep 2013

Hi,
Table Name : Table
Duplicate column : A
 
DELETE FROM TABLE WHERE
(
SELECT DISTINCT * FROM
     ( SELECT ROWID,T.* FROM TABLE T WHERE A IN ( SELECT A FROM TABLE GROUP BY A HAVING COUNT(1)>1)
)
I haven't tried it yet but I think this will help you.
 

siva yekula 1 post Joined 09/13
06 Sep 2013

what is collect stats in TD...?Expalin with Examples..thanq siva..

Qaisar Kiani 337 posts Joined 11/05
07 Sep 2013

Read the TD documentation, if you don't understand anything then post a specific question!!!!

You must sign in to leave a comment.