Interview Answer

Any code of your own that you havenít looked at for six or more months might as well have been written by someone else. Eaglesonís Law

Deleting duplicate rows from a table

January 1, 1970 12:00 am

Comments



Deleting duplicate rows from a table



												 desc HR.employees;
												 
												Name           Null     Type         
												-------------- -------- ------------ 
												EMPLOYEE_ID    NOT NULL NUMBER(6)    
												FIRST_NAME              VARCHAR2(20) 
												LAST_NAME      NOT NULL VARCHAR2(25) 
												EMAIL          NOT NULL VARCHAR2(25) 
												PHONE_NUMBER            VARCHAR2(20) 
												HIRE_DATE      NOT NULL DATE         
												JOB_ID         NOT NULL VARCHAR2(10) 
												SALARY                  NUMBER(8,2)  
												COMMISSION_PCT          NUMBER(2,2)  
												MANAGER_ID              NUMBER(6)    
												DEPARTMENT_ID           NUMBER(4)  

												

The following query delete the duplicate rows from the table.


Solution

   					 
												delete from hr.employees where rowid in
												(
												select rid from
												(
												select rowid rid,
												dense_rank() over(partition by employee_id order by rowid) rn
												from hr.employees
												)
												where rn > 1
												);
																    

Comments



Please login to add comments.