Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Unanswered: Finding Duplicate records and updating them

    I need to find duplicate records in a table, mark them as duplicates and print them out on a report. This is the code I have tried:

    select inv_num,po_num,wr_id,count(*) from vn_inv group by inv_num,po_num,wr_id having count(*) > 1

    It returns a count of 2 which is correct. I have two records that have the same inv_num, po_num, and wr_id. I need to mark them as duplicates. I have a field in the table called duplicate and I want to put a 'Y' in the field as it finds duplicates. So I tried the following code:

    update vn_inv set duplicate = 'Y' WHERE (select inv_num,po_num,wr_id,count(*) from vn_inv group by inv_num,po_num,wr_id having count(*) > 1);

    This code updates all records in the table instead of just the 2 duplicates.

    Is it possible to find the duplicates and update them?

    Thanks,
    SBR

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is it possible to find the duplicates and update them?
    Yes, I did it earlier this week.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    there are plenty of ways to do this but here is one using analytics:
    PHP Code:
    update vn_inv
    set duplicate 
    'Y'
    where rowid IN (
    select rowid from
        
    (select 
           inv_num
    po_numwr_idrowid,
           
    count(*) over 
            
    (partition by inv_numpo_numwr_id 
             order by inv_num
    po_numwr_idrownumscrolling_total,
           
    count(*) over 
            
    (partition by inv_numpo_numwr_idtotal
         from vn_inv
    )
        
    where total 1
        
    and scrolling_total 1
    ); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Finding Duplicate records and updating them

    Thank you for the code, I would never have figured that out. I tried it and this is what came back:

    SQL> update vn_inv set duplicate = 'Y' where rowid IN ( select rowid from (select inv_num,po_num,wr_
    id,rowid,count(*) over (partition by inv_num,po_num,wr_id order by inv_num,po_num,wr_id,rownum) scro
    lling_total,count(*) over (partition by inv_num, po_num, wr_id) total from vn_inv) where total > 1 a
    nd scrolling_total > 1);
    update vn_inv set duplicate = 'Y' where rowid IN ( select rowid from (select inv_num,po_num,wr_id,ro
    *
    ERROR at line 1:
    ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc.

    Thanks,
    SBR

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    How about..
    Code:
    update vn_inv
       set duplicate = 'Y'
     where wr_id in ( select wr_id
                     from (
                   select wr_id, count( * ) over( partition by wr_id, inv_num, po_num ) cnt
                     from vn_inv
                          )
                    where cnt > 1 )

  6. #6
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Finding Duplicate records and updating them

    Yes! That worked!

    UPDATE vn_inv set duplicate = 'Y' where wr_id in ( select wr_id from (select wr_id, count( * ) over( partition by wr_id, inv_num, po_num ) cnt from vn_inv) where cnt > 1);

    Thanks so much!!!!

    SBR

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Did you want to mark BOTH records as a duplicate or only the single duplicate record? If you wanted the second, then the code specified will not work.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    that's why you need the "total_count" analytic column
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Finding Duplicate records and updating them

    The update statement marked both duplicates which is what I wanted it to do. It works great.

    Will this same type of statement work with an INSERT statement? I need to check for an existing record in table ap_inv comparing it to table vn_inv. If the record in vn_inv does not exist in ap_inv, I need to insert it. I tried this statement but it gives an error "missing VALUES keyword".

    SQL> INSERT INTO ap_inv (inv_num,po_num) where wr_id in ( select wr_id from (select wr_id, count(*) over( partition by wr_id, inv_num, po_num ) cnt from vn_inv) where cnt > 1);

    INSERT INTO ap_inv (inv_num,po_num) where wr_id in ( select wr_id from (select wr_id, count(*) over(
    *
    ERROR at line 1:
    ORA-00926: missing VALUES keyword

    Thanks for your help,
    SBR

  10. #10
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What are you loading into the table. If you are selecting from a work table, the following will work

    INSERT INTO ap_inv (inv_num,po_num)
    select inv_num,po_num
    from test_table a
    where not exists
    (select null
    from vn_inv b
    where a.wr_id = b.wr_id);
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  11. #11
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Finding Duplicate records and updating them

    Actually the records I want to insert will be coming from the vn_inv table. I just need to check to make sure the records don't exist in the ap_inv table before I insert them.

    I tried this but it doesn't work.
    INSERT INTO ap_inv a (inv_num,po_num) (select inv_num,po_num from vn_inv where not exists (select null from vn_inv v where a.inv_num=v.inv_num and a.po_num=v.po_num));

    Thanks,

    SBR
    Last edited by sbr7770; 03-20-06 at 17:00.

  12. #12
    Join Date
    Jan 2004
    Posts
    492
    Define "didnt work" - error? Wrong data? I suspect its b/c of the extra parentheses around select - for some reason I think Oracle is picky about this.. Try this:

    Code:
    INSERT INTO ap_inv a (inv_num,po_num) 
    select inv_num,po_num 
     from vn_inv 
    where not exists (select null 
                              from vn_inv v 
                            where a.inv_num=v.inv_num and 
                                     a.po_num=v.po_num)
    Oracle OCPI (Certified Practicing Idiot)

  13. #13
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    Finding Duplicate records and updating them

    Thank you, it works!

    select inv_num,po_num from vn_inv v where not exists (select null from ap_inv a where a.inv_num=v.inv_num and a.po_num=v.po_num);


    Thanks again for your help,

    SBR

  14. #14
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    Follow-up to Ducks post

    just thought I would post this even though it is a bit late,

    the Duck's code will work if rownum is changed to rowid

    in this line ==> order by inv_num, po_num, wr_id, rownum) scrolling_total,

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    thanks for the fix. I knew something was incorrect in there and that it should work.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •