SQL Server Performance

Delete Duplicate Rows in Oracle & Sql Server

Discussion in 'Contribute Your SQL Server Scripts' started by bradmcgehee, Mar 5, 2004.

  1. bradmcgehee New Member

    How to Delete Duplicate Rows in Oracle and Sql Server

    This question is very common now a days being asked by various database administers and developers and different people give their different opinions in shape of using stored procedures, cursors and loops etc.

    You can do it with simpls sql query as under:-

    Oracle

    In Oracle, you can use RowID Datatype to Delete All Duplicate Rows, leaving behind one Record for each Duplicated Row.

    Create Table myTable
    (name varchar2(10),
    add1 varchar2(10),
    add2 varchar2(10));

    Insert into myTable
    values('Jacky','Room 25','Drig Road');

    Insert into myTable
    values('Jack2','Room 29','Drig Road');

    Insert into myTable
    values('Jacky','Room 25','Drig Road');

    Delete from myTable
    where rowid not in
    (select min(rowid) from
    myTable
    group by name,add1,add2);


    Sql Server

    As there is no such RowID type in Sql Server like Oracle, so you have to create an Identity Column with other fields as under:-

    Create Table myTable
    (myID int identity,
    name varchar(10),
    add1 varchar(10),
    add2 varchar(10))

    go

    Insert into myTable
    values('Jacky','Room 25','Drig Road')

    go

    Insert into myTable
    values('Jack2','Room 29','Drig Road')

    go

    Insert into myTable
    values('Jacky','Room 25','Drig Road')

    go

    Delete from myTable
    where myID not in
    (select min(myID) from myTable
    group by name,add1,add2)

    go


    Further, if in case of sql server, you don't have identity column in your table, then you can add it without deleting the existing data with the following sql, before running the above Delete Query.

    alter table mytable
    add myid int identity


    Best Regards,
    Mohammad Luqman Usman (CEO)
    M/s. Pearl Soft, Karachi, Pakistan
  2. casper New Member

    Dear Bradmcgehee

    Thank you. Deleting statement is really useful to me.



    Best Regards,
    casper

    casper
  3. Dear Bradmcgehee,

    Thanks for your Tips. But I have a question. If my Table Structure would have been created without any Identity Column then how the deletion operation on duplicate Record is possible with a single query.

    Thanks & Regards,

    Arindam Ganguly
  4. satya Moderator

  5. Madhivanan Moderator

    Thanks brad for your methods.
    Satya, your link provides lot of methods to delete duplicates. Thanks


    Madhivanan

    Failing to plan is Planning to fail
  6. cspaladugu New Member

    Perhaps this is one of the best methods of deleting duplicates Thanks Mr. bradmcgehee

    Chandra Paladugu
  7. dineshasanka Moderator

    Yes This is a hot issue in the forum for last months
    There are few more articles as well

    www.sqlservercentral.com/columnists/ccubley/findinganddeletingduplicatedata.asp
    www.sqlservercentral.com/columnists/darjun/deletingduplicaterows.asp
    www.sqlservercentral.com/columnists/sramakrishnan/deletingduplicaterecords.asp

  8. sabeerpasha New Member

    Sample table name – TEST
    Structure – as belowSNO SNAME
    ---- ---------
    1001 ATHER
    1001 ATHER
    1001 ATHER
    1001 ATHER
    1001 ATHER

    Query to delete duplicate records can be written as given below

    SQL> delete from test a
    where rowid <> ( select max(rowid)
    from test b
    where a.sno = b.sno
    and a.sname = b.sname )
    Regards,
    sabeer pasha.

Share This Page