Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    India
    Posts
    55

    Unhappy Unanswered: Duplicate Records

    Hi Folks,

    Got problem........

    How to delete duplicate records in oracle table.

    Supose i have the value 'abc' 10 times in a table i should retain only one of it.

    Thanx in advance
    Sree

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    for the many dups you'd better first create a temp table

    create table dups as select * from sourcetab group by col1, col2, ...
    having count(*) > 1;

    second
    delete from sourcetab where (col1, col2, ...) in (select col1, co2, ... from dups

    you must then put one row back

    insert into sourcetab (col11, col2 ...) select * from dups; but create a unique constraint first
    Last edited by osy45; 05-29-04 at 11:25.

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    This is fairly standard dupe delete code...

    Code:
    delete from yourtable where rowid not in (
    	  select min(rowid) from yourtable group by col1,col2,.....);
    Change the inner select to group by the necessary columns.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

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