Can somebody please push me in right direction

Question

My code below to delete duplicate entries

with mytable as
(
select a.*,
row_number() over(partition by emp_id order by emp_id) as Row_number 
from employee_Details2 a 
)
delete from mytable
where Row_number=2;

Error:

*ORA-00928: missing SELECT keyword
00928. 00000 -  "missing SELECT keyword"
*Cause:    
*Action:
Error at Line: 43 Column: 1*

Answer 1

You can't delete from a CTE.

If it is about deleting from employee_details2, then see if this helps:

delete from employee_details2 a
where a.rowid > (select min(b.rowid)
                 from employee_details2 b
                 where b.empid = a.empid
                );

---

If you want to delete rows using "row number" (as you put it), well - yes, you can do that, but what I suggested originally is simpler and better.

SQL> select * From test order by deptno;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 06/09/1981       2450                    10
      7839 KING       PRESIDENT            11/17/1981      10000                    10
      7934 MILLER     CLERK           7782 01/23/1982       1300                    10
      7566 JONES      MANAGER         7839 04/02/1981       2975                    20
      7902 FORD       ANALYST         7566 12/03/1981       3000                    20
      7876 ADAMS      CLERK           7788 01/12/1983       1300                    20
      7369 SMITH      CLERK           7902 12/17/1980        920                    20
      7788 SCOTT      ANALYST         7566 12/09/1982       3000                    20
      7521 WARD       SALESMAN        7698 02/22/1981       1250        500         30
      7844 TURNER     SALESMAN        7698 09/08/1981       1500          0         30
      7499 ALLEN      SALESMAN        7698 02/20/1981       1600        300         30
      7900 JAMES      CLERK           7698 12/03/1981        950                    30
      7698 BLAKE      MANAGER         7839 05/01/1981       2850                    30
      7654 MARTIN     SALESMAN        7698 09/28/1981       1250       1400         30

14 rows selected.

SQL> delete from test b
  2  where b.empno in (select c.empno
  3                    from (select a.empno, row_number() over (partition by a.deptno order by a.empno) rn
  4                          from test a
  5                         ) c
  6                    where c.rn > 1
  7                   );

11 rows deleted.

SQL> select * From test order by deptno;

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 06/09/1981       2450                    10
      7369 SMITH      CLERK           7902 12/17/1980        920                    20
      7499 ALLEN      SALESMAN        7698 02/20/1981       1600        300         30

SQL>

Answer 2

The delete can not work with CTE, You need to separate the logic of DELETE and SELECT as follows:

delete from employee_Details2
where rowid in 
(select rowid from
( select a.rowid, a.*, 
         row_number() over(partition by emp_id order by emp_id) as Row_number 
from employee_Details2 a )
where Row_number = 2)