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

    How to delete duplicate rows in sql server

    how to delete duplicate rows in sql server if the table has no primary key or any identity column..plz help me
  • #586084
    here is the example of delete duplication in table so try it


    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

  • #586098
    When iam writing the above query iam inserted table values like this

    1 bhanu 2000.0000
    2 prakash 3000.0000
    1 bhanu 2000.0000
    3 akash 2000.0000
    2 prakash 3000.0000

    when executing this query

    set rowcount 1
    select 'start'
    while @@rowcount > 0 delete a from a where (select count(*) from emp b where a.empno = b.empno)>1
    set rowcount 0

    the error is displayed as

    (1 row(s) affected)
    Msg 208, Level 16, State 1, Line 3
    Invalid object name 'a'.


    then after iam executing the query select * from emp

    it displays only one record

    1 bhanu 2000.00

    but in table the columns are same as starting values

    plz change my query if i done any mistake

  • #586101
    select distinct * from tablename
    Chirag - 48
    Enjoy With Errors
    chirag.madhani@yahoo.com

  • #586108

    DELETE
    FROM MyTable
    WHERE ID NOT IN
    (
    SELECT MAX(ID)
    FROM MyTable
    GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

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

  • #586766
    remove duplicate rows from a SQL Server table



    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


  • 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.