Make use of SET operators and CTAS (Create Table <> AS) method..
Example:
Create table tmp_tb1
as
Select * from table1
intersect
Select * from table1;
truncate table table1;
insert into table1
select * from tmp_tb1; -- This will now have removed all duplicates in the table...
commit;
*** You can use UNION operator in place of INTERSECT
Example:
Create table tmp_tb1
as
Select * from table1
intersect
Select * from table1;
truncate table table1;
insert into table1
select * from tmp_tb1; -- This will now have removed all duplicates in the table...
commit;
*** You can use UNION operator in place of INTERSECT
Comments
Straightforward method is to query as below..
DELETE
FROM EMP E1
WHERE ROWID <
( SELECT MAX(ROWID) FROM EMP WHERE ENAME=E1.ENAME
);
DELETE
FROM EMP E1
WHERE ROWID >
( SELECT MIN (ROWID) FROM EMP WHERE ENAME=E1.ENAME
);
Query structure:
Delete from Alias
where rowid < ( select max(rowid) from where );
Your comments are welcome!!!