1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to fetch duplicate rows with out using rowid

Discussion in 'SQL PL/SQL' started by manoj.vajpee, Dec 18, 2014.

  1. manoj.vajpee

    manoj.vajpee Active Member

    Messages:
    11
    Likes Received:
    1
    Trophy Points:
    110
    How to fetch duplicate rows with out using rowid
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    DELETE FROM TABLENAME WHERE (COLUMNS-TO-COMPARE) IN (SELECT COLUMNS-TO-COMPARE FROM TABLENAME GROUP BY COLUMNS-TO-COMPARE HAVING COUNT(*) > 1) AND ROWNUM < ( SELECT COUNT(*) FROM TABLENAME GROUP BY COLUMNS-TO-COMPARE HAVING COUNT(*) > 1

    It may help you...
     
    manoj.vajpee likes this.
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Finding rows with duplicate keys is easy. Knowing WHICH of those rows to delete isn't. Simply because the keys occur twice doesn't necessarily mean the entire rows are duplicate. You could easily delete the data you want to keep with a simple statement like the one posted. You should never rely on a script to delete 'duplicates'; that's work you need to do by hand to verify you are keeping the correct rows.
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    David:

    That script actually works fairly well. I remember it from my days in Oracle support. It's not looking for keys, the 'COLUMNS-TO-COMPARE' is replaced by whatever set of columns is supposed to be unique -- whether that's two columns or a hundred and two. The query then deletes all duplicates except the one with the lowest ROWID. Mind you, I always highly recommended that users run it as a SELECT first to find which rows were going to be deleted:

    Code (Text):
    SELECT *
    FROM   TABLENAME
    WHERE (COLUMNS-TO-COMPARE) IN (SELECT COLUMNS-TO-COMPARE
                                   FROM   TABLENAME
                                   GROUP BY COLUMNS-TO-COMPARE
                                   HAVING COUNT(*) > 1)
    AND ROWNUM < ( SELECT COUNT(*)
                   FROM   TABLENAME
                   GROUP BY COLUMNS-TO-COMPARE
                   HAVING COUNT(*) > 1;
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I know it returns 'duplicates' based on comparison columns. My comment is directed at the rest of the data in that row; I have come across many examples where keys were duplicated yet the rest of the data in the 'duplicate' rows was not the same. Deleting rows based only on the fact that the key column values are duplicated could delete the most current data. Care must be taken before deleting 'duplicates'.
     
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    David:

    I see the distinction you're making. I have really only seen (or suggested) it be used for 'true' duplicates -- i.e. a table contains ten columns and the 'COLUMNS-TO-COMPARE' list is ten columns (or possibly nine if the table contains a meaning free primary key column -- like a sequence). Used in that fashion, you can't be deleting anything except a true duplicate.

    Certainly for a table that contained one (or two or three or five) columns that are intended to be unique in a table with a total of ten columns -- using this in the DELETE mode would be quite dangerous.