Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    5

    Unanswered: Deleting latest duplicate records using rowid

    Hi,

    Could any one tell me if i have duplicate rows and i will run the query below and it deletes all teh duplicates and leave the one record but i just wanted to know is the remained one record is the one which is inserted first into the table or the one which last inserted, i mean is the oracle will reatins the oldest record or it delete randomly and retains any one among them.
    if so what should be the query which retains the oldest among the duplicates please any one can help me..

    delete from mytable t1
    where rowid > (select min(rowid) from mytable t2 where t2.key = t1.key);

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oracle has no knowledge of "oldest" or "latest" records. Your DELETE will keep the record with the lowest ROWID value, but ROWID value is no indication of age, only of position on disk. Essentially it is arbitrary as to which record will be kept.

    Of course, if your table has an audit column like CREATED_DATE on it then you could amend your SQL to:

    delete from mytable t1
    where created_Date < (select max(created_date) from mytable t2 where t2.key = t1.key);

    (to keep the latest record).

Posting Permissions

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