Skip to main content

How to remove duplicate rows without using DISTINCT or ROWID or GROUP BY methods in Oracle?

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

Comments

Khaja V Shaik said…
how does this work, if the duplicates on particular columns need to be deleted... I believe this is not a viable approach.
January 9, 2012 at 8:46 AM
SK said…
Khaja.. Thanks for your 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!!!
January 9, 2012 at 9:12 AM
Post a Comment

Popular posts from this blog

Design and Operations ( Employee- Model )

-- DB design drop table emp_Salary; drop table emp_Designation; drop table emp; drop table Designations; drop table Departments; drop table Salary_Bands; drop table Addresses; Create table emp( emp_id number, emp_Name varchar2(20), emp_address_id varchar2(10), emp_created_date date, emp_created_by varchar2(20), constraint e_pk primary key(emp_id) ); Create table Departments( dept_id number, dept_name Varchar2(20), constraints dept_pk primary key(dept_id) ); Create table Designations( dept_id number references Departments(dept_id), desg_id number, desg_description varchar2(20), constraints desg_pk primary key(dept_id,desg_id) ); Create table emp_Designation( emp_id number references emp(emp_id), emp_des_dept_id number references departments(dept_...
Post a Comment
Read more

Identify current and prev details of a customer

  create table customer_rating( id char, name char, irrating char, jcrrating char, procDate date, flag char ); insert into customer_rating values('A','D','X','Y','28-Jul-21','M'); insert into customer_rating values('A','D','M','L','27-Jul-21','M'); Select cr.id, cr.name, cr_prev.irrating pcob_rating, cr.irrating cob_rating, cr_prev.jcrrating pcob_jcr_rating, cr.jcrrating cob_jcr_rating From Customer_Rating cr join Customer_Rating cr_Prev  ON cr.procdate = '28-Jul-21' AND cr_prev.id = cr.id AND cr_prev.procdate = (  Select max(procdate) from Customer_Rating  Where id=cr.id and procdate < cr.procdate ) ;
Post a Comment
Read more