Hi All,
For Delete, I would like to refer data from multiple tables using a Join and just delete the matching data from a single table, the below Delete statement is just an example, I get many similar scenarios like this, how do we do it in Oracle, if not support and what is the alternative? Could you please give your comments? thank you.
CREATE TABLE EMP_1000
( EMP_ID NUMBER(4),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
GENDER CHAR(1)
);
Insert into EMP_1000 (EMP_ID,FIRST_NAME,LAST_NAME,GENDER) values (100,'Name 100 - 1','LN100','M');
Insert into EMP_1000 (EMP_ID,FIRST_NAME,LAST_NAME,GENDER) values (100,'Name 100 - 2','LN100','M');
Insert into EMP_1000 (EMP_ID,FIRST_NAME,LAST_NAME,GENDER) values (100,'Name 100 - 3','LN100','M');
Insert into EMP_1000 (EMP_ID,FIRST_NAME,LAST_NAME,GENDER) values (101,'Name 101 - 1','LN101','M');
Insert into EMP_1000 (EMP_ID,FIRST_NAME,LAST_NAME,GENDER) values (101,'Name 101 - 2','LN101','M');
-- the below SQL gives the duplicate data identified for deletion and I would like to delete this data alone using the second delete
-- statement given below.
select a.* from emp_1000 a,
(select emp_id, min(rowid) min_rowid from emp_1000
group by emp_id) b
where a.emp_id=b.emp_id and a.rowid <> b.min_rowid;
-- the below co-related delete query works well
delete from emp_1000 a
where rowid > (select min(rowid) from emp_1000 b where a.emp_id=b.emp_id);
-- the below delete fails in Oracle, which works well in other databases like Teradata and SQL Server
delete a from emp_1000 a inner join
(select emp_id, min(rowid) min_rowid from emp_1000
group by emp_id) b
on a.emp_id=b.emp_id
where a.rowid <> b.min_rowid;