Deleting duplicate data from table in oracle

Question

I have a table duplicate which shows datas as:

select * from ot.duplicate;

I wanted to delete the duplicate data from the table where I tried:

delete  from (select * from(select * from ot.duplicate a
union
select * from ot.duplicate b) t1);

But I am getting error:

ORA-01752: cannot delete from view without exactly one key-preserved table

Answer 1

You can use exists.

Delete from your_table y1
Where exists (select 1 
From your_table y2
Where y1.id = y2.id
Abd y1.name = y2.name
And y1.rowid > y2.rowid);

Cheers!!

Answer 2

You should try below query -

DELETE FROM ot.duplicate A
WHERE ROWID > (SELECT MIN(ROWID)
               FROM ot.duplicate B
               WHERE A.ID = B.ID
               AND A.NAME = B.NAME)

Answer 3

It should have been like this in SQL Server

;with cte as (
select * from (
select id,name ,ROW_NUMBER() over(partition by name order by id) as trow from testt
) as p  
)

delete from cte where trow>1