3 Replies Latest reply: Jan 23, 2013 9:19 PM by 986811 RSS

    Duplicates:

    983563
      select * from emp_test

      Hi experts how can i do this
                     
      i want to delete the records which are duplicated having more than one records with same ename (all the fields are same except empno)

                     
                     
                     
                     INSERT INTO EMP_TEST ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
                     DEPTNO ) VALUES ( 
                     7934, 'MILLER', 'CLERK', 7782,  TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
                     , 1300, NULL, 10); 
                     COMMIT;
                     
                     INSERT INTO EMP_TEST ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
                     DEPTNO ) VALUES ( 
                     7935, 'MILLER', 'CLERK', 7782,  TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
                     , 1300, NULL, 10); 
                     
                     
                     INSERT INTO EMP_TEST ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
                     DEPTNO ) VALUES ( 
                     7936, 'MILLER', 'CLERK', 7782,  TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
                     , 1300, NULL, 10);
                     
                     select * from emp_test 
                    
        • 1. Re: Duplicates:
          sb92075
          980560 wrote:
          select * from emp_test

          Hi experts how can i do this
                         
          i want to delete the records which are duplicated having more than one records with same ename (all the fields are same except empno)

                         
                         
                         
                         INSERT INTO EMP_TEST ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
                         DEPTNO ) VALUES ( 
                         7934, 'MILLER', 'CLERK', 7782,  TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
                         , 1300, NULL, 10); 
                         COMMIT;
                         
                         INSERT INTO EMP_TEST ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
                         DEPTNO ) VALUES ( 
                         7935, 'MILLER', 'CLERK', 7782,  TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
                         , 1300, NULL, 10); 
                         
                         
                         INSERT INTO EMP_TEST ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
                         DEPTNO ) VALUES ( 
                         7936, 'MILLER', 'CLERK', 7782,  TO_Date( '01/23/1982 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
                         , 1300, NULL, 10);
                         
                         select * from emp_test 
          which record (empno) should remain & why?
          • 2. Re: Duplicates:
            Frank Kulash
            Hi,

            This leaves the row with the lowest empno, and deletes the other rows where the other columns are the same:
            DELETE     emp_test
            WHERE     empno     NOT IN
            (
                 SELECT       MIN (empno)
                 FROM       emp_test
                 GROUP BY  ename
                 ,            job
                 ,       mgr
                 ,       hiredate
                 ,       sal
                 ,       comm
            );
            I assume that you consider NULL to be "the same" as NULL.

            Edited by: Frank Kulash on Jan 23, 2013 10:14 PM
            • 3. Re: Duplicates:
              986811
              CREATE TABLE TMP_EMP_TEST AS
              SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
              FROM (SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO,
              ROW_NUMBER() OVER(PARTITION BY ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ORDER BY EMPNO) AS RN
              FROM EMP_TEST)
              WHERE RN = 1;

              TRUNCATE TABLE EMP_TEST; -- YOU MAY NEED TO BACKUP

              INSERT INTO EMP_TEST
              SELECT * FROM TMP_EMP_TEST;

              DROP TABLE TMP_EMP_TEST;