Deleting duplicate rows in multiset table

Database
Enthusiast

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 ?

9 REPLIES
Enthusiast

Re: Deleting duplicate rows in multiset table

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.

Enthusiast

Re: Deleting duplicate rows in multiset table

Thanks Harpreet.

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

Enthusiast

Re: Deleting duplicate rows in multiset table

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.

Enthusiast

Re: Deleting duplicate rows in multiset table

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..

Junior Contributor

Re: Deleting duplicate rows in multiset table

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

Enthusiast

Re: Deleting duplicate rows in multiset table

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

N/A

Re: Deleting duplicate rows in multiset table

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.

Re: Deleting duplicate rows in multiset table

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

Enthusiast

Re: Deleting duplicate rows in multiset table

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