10 August 2011

Eliminate Duplicate Records


Hi guys,
This time our task is to remove or eliminate duplicate entries from a sql Table.
Consider we have a Table Say Employee which contain some duplicate entries,and we have to eliminate the duplicate ones.


So lets start thinking..

5 comments:

  1. SELECT DISTINCT * INTO #temp FROM Employee

    TRUNCATE TABLE Employee

    INSERT INTO Employee
    SELECT * FROM #temp

    ReplyDelete
  2. without subquery
    ----------------------------

    Declare @table1 table (
    col1 varchar(40),
    col2 varchar(40)
    )

    Insert into @table1
    values('1','sai')
    Insert into @table1
    values('2','suk')
    Insert into @table1
    values('1','sai')

    Declare @table2 table (
    col1 varchar(40),
    col2 varchar(40)
    )
    Insert into @table2
    select t.col1,t.col2 from @table1 As t group by t.col1, t.col2

    delete from @table1


    Insert into @table1
    select t.col1,t.col2 from @table2 AS t

    select * from @table1

    ReplyDelete
  3. Declare @table1 table (
    id int,
    col1 varchar(40),
    col2 varchar(40)
    )

    Insert into @table1
    values(1,'1','sai')
    Insert into @table1
    values(2,'2','suk')
    Insert into @table1
    values(3,'1','sai')

    Delete from @table1 where id not in (select max(t.id) from @table1 As t group by t.col1, t.col2)

    select * from @table1

    --select min(t.id) from @table1 As t group by t.col1, t.col2

    ReplyDelete
  4. Declare @table1 table (
    col1 varchar(40),
    col2 varchar(40)
    )

    Insert into @table1
    values('1','sai')
    Insert into @table1
    values('2','suk')
    Insert into @table1
    values('1','sai')

    Declare @table2 table (
    col1 varchar(40),
    col2 varchar(40)
    )
    Insert into @table2
    select t.col1,t.col2 from @table1 As t group by t.col1, t.col2

    delete from @table1


    Insert into @table1
    select t.col1,t.col2 from @table2 AS t

    select * from @table1

    ReplyDelete
  5. delete d1
    from duplicate_records d1
    join duplicate_records d2 on d1.dupname=d2.dupname
    and d1.id>d2.id

    This query deletes the duplicate records keeping the records with lowest ids.

    It is useful when the table contains very few records as this query is similar to a cross join and hence the complexity is greater.

    ReplyDelete

Your comments, Feedbacks and Suggestions are very much valuable to me :)

Things are upgraded

My Dear readers, I am really thankful for being supportive all these years. This site was the first blog site I ever created in my life...