8 Replies Latest reply: Dec 5, 2012 4:08 AM by suzvino RSS

    find duplicate and want to just update one of them

    suzvino
      How to find duplicate and want to just update one of them.

      i have a emp table with columns emp_id ,emp_no and duplicate_col ...
      Emp_id has unique constraint , but emp_no got duplicated. I want to find these duplicate emp_no and want to update one of emp_id with duplicate_flag as 'Y'

      Thanks
      Vinoth.
        • 1. Re: find duplicate and want to just update one of them
          972756
          You can run

          with dup as (select emp_no ,min(rowid) as r_id from emp group by emp_no having count(*)>1)
          update emp
          set duplicate_flag ='Y'
          where emp_no in (select emp_no from dup)
          and rowid not in (select r_id from dup);

          This should update them even if there are 3 or more records for each emp_no, levaing just one unflagged.
          • 2. Re: find duplicate and want to just update one of them
            suzvino
            I am using 11g

            Sorry. Let me say my requirement.

            I dont want update the column. I just want to select duplicated list except one in that.

            If i have emp_id having same employee number- emp_id 100, 101, 102 has employee number 900
            then i want to list except one of these three... 100, 101.. even 101 , 102 is also fine.. any two emp_id except one..

            Employee number can in n records emp_id but i want n-1 records except the one....

            THanks
            Vinoth
            • 3. Re: find duplicate and want to just update one of them
              jeneesh
              select *
              from test_emp;
              
              EMP_ID EMP_NO DUP_FLAG
              ------ ------ --------
                   1      1          
                   2      2          
                   3      3          
                   4      4          
                   5      4          
                   6      4          
                   7      3          
                   8      8          
                   9      9          
                  10     10          
              
               10 rows selected 
              
              merge into test_emp a
              using (
                select b.*,row_number() over(partition by emp_no order by emp_id) rn
                from test_emp b
                    ) b
              on (a.emp_id = b.emp_id and rn > 1)
              when matched then 
               update set a.dup_flag = 'Y';
              
              
              select *
              from test_emp;
              
              EMP_ID EMP_NO DUP_FLAG
              ------ ------ --------
                   1      1          
                   2      2          
                   3      3          
                   4      4          
                   5      4 Y        
                   6      4 Y        
                   7      3 Y        
                   8      8          
                   9      9          
                  10     10          
              
               10 rows selected 
              • 4. Re: find duplicate and want to just update one of them
                jeneesh
                suzvino wrote:
                I am using 11g

                Sorry. Let me say my requirement.

                I dont want update the column. I just want to select duplicated list except one in that.

                If i have emp_id having same employee number- emp_id 100, 101, 102 has employee number 900
                then i want to list except one of these three... 100, 101.. even 101 , 102 is also fine.. any two emp_id except one..

                Employee number can in n records emp_id but i want n-1 records except the one....

                THanks
                Vinoth
                with emp_data as
                (
                    select b.*,row_number() over(partition by emp_no order by emp_id) rn
                    from test_emp b
                )
                select *
                from emp_data
                where rn > 1;
                
                EMP_ID EMP_NO DUP_FLAG RN
                ------ ------ -------- --
                     7      3 Y         2 
                     5      4 Y         2 
                     6      4 Y         3 
                Edited by: jeneesh on Dec 5, 2012 2:53 PM
                Output using the data in the previous post
                • 5. Re: find duplicate and want to just update one of them
                  AlbertoFaenza
                  Hi,

                  welcome to the forum.

                  Please read SQL and PL/SQL FAQ

                  Additionally when you put some code please enclose it between two lines starting with {noformat}
                  {noformat}
                  i.e.:
                  {noformat}
                  {noformat}
                  SELECT ...
                  {noformat}
                  {noformat}
                  
                  Here is one way:
                  DROP TABLE test_emp;

                  CREATE TABLE test_emp
                  (
                  emp_id NUMBER PRIMARY KEY
                  , emp_no VARCHAR2 (10)
                  , duplicate VARCHAR2 (1)
                  );

                  INSERT INTO test_emp VALUES(100, 'A0001', NULL);
                  INSERT INTO test_emp VALUES(101, 'A0001', NULL);
                  INSERT INTO test_emp VALUES(102, 'A0002', NULL);
                  INSERT INTO test_emp VALUES(103, 'A0002', NULL);
                  INSERT INTO test_emp VALUES(104, 'A0002', NULL);
                  INSERT INTO test_emp VALUES(105, 'A0003', NULL);

                  COMMIT;

                  UPDATE test_emp a
                  SET duplicate='Y'
                  WHERE emp_id IN (SELECT emp_id
                  FROM (SELECT b.emp_id
                  , ROW_NUMBER() OVER (PARTITION BY emp_no ORDER BY emp_id) rn
                  FROM test_emp b
                  )
                  WHERE rn > 1
                  );

                  SELECT *
                  FROM test_emp;

                  EMP_ID EMP_NO DUPLICATE
                  ---------- ---------- ---------
                  100 A0001
                  101 A0001 Y
                  102 A0002
                  103 A0002 Y
                  104 A0002 Y
                  105 A0003
                  Regards.
                  Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                  • 6. Re: find duplicate and want to just update one of them
                    Purvesh K
                    This way (Untested):
                    Since you said, you need to update One of the EMP_ID as Duplicate, following shall suffice. If you need to exclude one and mark others as Duplicate use "e1.rn > 1"
                    merge into emp e
                    using (
                              select emp_id, emp_no, row_number() over (partition by emp_no order by emp_id) rn
                               from emp
                           ) e1
                    on ( e.emp_no = e1.emp_no )
                    when matched then
                      update set duplicate = 'Y' where e1.rn = 1;
                    • 7. Re: find duplicate and want to just update one of them
                      suzvino
                      Thanks for all the replies...

                      I followed Jeneesh one..
                      select *
                      from (
                          select b.*,row_number() over(partition by emp_no order by emp_id) rn
                          from TEST_EMP B
                      )
                      where rn > 1;
                      This gave me records 25000+ records where i was expecting 3500+ records

                      i dont want to merge com mand. I dont want to update the table. I just want to emp_id of n-1 records which i will process further.

                      Thanks
                      Vinoth
                      • 8. Re: find duplicate and want to just update one of them
                        jeneesh
                        suzvino wrote:
                        Thanks for all the replies...

                        I followed Jeneesh one..
                        select *
                        from (
                        select b.*,row_number() over(partition by emp_no order by emp_id) rn
                        from TEST_EMP B
                        )
                        where rn > 1;
                        This gave me records 25000+ records where i was expecting 3500+ records

                        i dont want to merge com mand. I dont want to update the table. I just want to emp_id of n-1 records which i will process further.

                        Thanks
                        Vinoth
                        Please provide the output of the below
                        select count(*)
                        from
                         (select emp_no
                          from your_table
                          group by emp_no
                          having count(*)  > 1
                         )