running delete command to remove duplicate in oracle with cte is giving ora-00928 error

Question

I am runnig the below query to delete duplicate from my table, but its giving the below error:

with cte as (select  name,address,designation, row_number() over(partition by name,address,designation order by name) rn from emp)
delete from cte where rn <>1;
select * from emp;

Error: ORA-00928: missing SELECT keyword

How to fix this?

Does this answer your question? [Removing duplicate rows from table in Oracle](https://stackoverflow.com/questions/529098/removing-duplicate-rows-from-table-in-oracle) — Koen Lostrie, Apr 03 '21 at 08:53

score 2 · Answer 1 · answered Apr 03 '21 at 10:24

You can't delete from a CTE or an inline view.

What I always use:

delete emp
where  rowid in
       ( select lag(rowid) over (partition by name,address,designation order by name)
         from   emp );

There are a lot of more complicated approaches over on Removing duplicate rows from table in Oracle

running delete command to remove duplicate in oracle with cte is giving ora-00928 error

1 Answers1