duplicate rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: duplicate rows

  1. #1
    Join Date
    Jul 2001
    Posts
    181

    duplicate rows

    Hi, I have a table which has duplicate rows and I want to put a primary key on two of the columns say col A and B (composite key). The problem I have is I can remove duplicate rows with the script below from col A

    e.g
    delete from &&table_name t1
    where t1.&&column_name in (select &&column_name,
    from &&table_name t2
    where t1.rowid < t2.rowid
    and t1.&&column_name = t2.&&column_name)

    This will remove all duplicates from col A however there is some data
    in col B which is needed and when combined with col A make it unique
    see the example below. when I run the above remove script. It removes one of the 5 entries which is not what I want!

    which is
    A B C
    5 123
    5 124
    6 567
    7 568


    How do I do this?

    Thanks

  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    ... where (t1.a, t1.b) in (select a,b from ...)
    Best wishes!
    Dmitri

  3. #3
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    delete from t1 a where a.rowid in (select a.rowid from t1 b where a.a=b.a and a.b=b.b and a.rowid
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  4. #4
    Join Date
    Jul 2001
    Posts
    181
    Problem resolved
    Many Thanks.


    If a man is sufficiently unimaginative to produce evidence in support of a lie, he might just as well speak the truth at once.

    Oscar Wilde
    --The Decay of Lying

Posting Permissions

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



Click Here to Expand Forum to Full Width