This discussion is archived
8 Replies Latest reply: Dec 5, 2012 2:08 AM by suzvino RSS

find duplicate and want to just update one of them

suzvino Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
     )

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points