You must Sign In to post a response.
  • Category: SQL Server

    How to remove duplicate records from a table?

    How to remove duplicate records from a table?
  • #586708

    SELECT col1, col2, count(*)
    FROM t1
    GROUP BY col1, col2
    HAVING count(*) > 1

    All is well.
    Thanks,
    Dharma
    Editor,Mentor,MVM
    Try and fail but don't fail and try

  • #586723

  • #586740
    hi..

    use DISTINCT while selecting the records.
    Select DISTINCT col1, Col2..
    FROM table1

    Regards,
    Divyashree.k
    Experience is the name every one gives to their mistakes.

  • #586743

  • #586748
    to remove duplicate rows from a SQL Server table by using a script



    SELECT DISTINCT *
    INTO duplicate_table
    FROM original_table
    GROUP BY key_value
    HAVING COUNT(key_value) > 1

    DELETE original_table
    WHERE key_value
    IN (SELECT key_value
    FROM duplicate_table)

    INSERT original_table
    SELECT *
    FROM duplicate_table

    DROP TABLE duplicate_table





    1.It moves one instance of any duplicate row in the original table to a duplicate table.
    2.It deletes all rows from the original table that also reside in the duplicate table.
    3.It moves the rows in the duplicate table back into the original table.
    4.It drops the duplicate table.

  • #586756

  • #586808
    hi this code may help you



    create table tbl1
    (
    col1 int
    )
    insert into tbl1 values(1)
    insert into tbl1 values(1)
    insert into tbl1 values(1)
    insert into tbl1 values(2)
    insert into tbl1 values(2)
    insert into tbl1 values(2)
    insert into tbl1 values(2)
    insert into tbl1 values(3)
    insert into tbl1 values(3)
    ---------Delete Duplicate Rows------------
    set rowcount 1
    select 'start'
    while @@rowcount > 0 delete a from tbl1 a where (select count(*) from tbl1 b where a.col1 = b.col1)>1
    set rowcount 0
    --------------------
    select * from tbl1

    select * from tbl1



    hope this will help you

  • #586900
    Hi,

    Try the below query. Hope it help you.


    DELETE FROM TABLE1 WHERE COL1 IN (SELECT COL1 FROM TABLE1 GROUP BY COL1 HAVING COUNT(1) > 1)

    Thanks & Regards
    Usha R


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.