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

    How to delete the duplicate records from table in sqlserver

    Hi,

    How to delete the duplicate records from table in sqlserver.

    Regards,
    venkat
  • #758081
    Hi venkat

    If you have a original table let us say "emp_details" with duplicate records.

    Then to eliminate duplicates we can use distinct.

    select distinct * from emp_details

    we wil have the table with out duplicates.
    Now copy those unique records in to a temporary table let us say "newEmp_details".

    select * into newEmp_details
    from(select distinct * from emp_details)a

    Now your newEmp_details table will have records with out duplicates.

    Delete emp_details table. Delete command deletes data but table structure will remain.

    copy the newEmp_details table data in to original table emp_details.

    regards

    sridhar.
    DNS Member.

    Sridhar Thota.
    Editor: DNS Forum.

  • #758087
    Hi Venkateswrao,

    WITH TempUsers (FirstName,LastName, duplicateRecordCount)
    AS
    (
    SELECT FirstName,LastName,
    ROW_NUMBER()OVER(PARTITIONBY FirstName, LastName ORDERBY FirstName) AS duplicateRecordCount
    FROM dbo.Users
    )
    DELETE
    FROM TempUsers
    WHERE duplicateRecordCount > 1
    GO

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #758109
    You need to first retrieve duplicate rows from table, for that you can use below query

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

    after retrieving duplicate records, its time to delete it

    set rowcount 1
    delete from t1
    where col1=1 and col2=1

    OR you can use following Query too

    select distinct * into #tmp From EmpDup
    delete from EmpDup
    insert into EmpDup
    select * from #tmp drop table #tmp

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #758122
    HI,

    USE tempdb
    GO
    CREATE TABLE TestTable (ID INT, NameCol VARCHAR(100))
    GO
    INSERT INTO TestTable (ID, NameCol)
    SELECT 1, 'First'
    UNION ALL
    SELECT 2, 'Second'
    UNION ALL
    SELECT 3, 'Second'
    UNION ALL
    SELECT 4, 'Second'
    UNION ALL
    SELECT 5, 'Second'
    UNION ALL
    SELECT 6, 'Third'
    GO
    -- Selecting Data
    SELECT *
    FROM TestTable
    GO
    -- Detecting Duplicate
    SELECT NameCol, COUNT(*) TotalCount
    FROM TestTable
    GROUP BY NameCol
    HAVING COUNT(*) > 1
    ORDER BY COUNT(*) DESC
    GO
    -- Deleting Duplicate
    DELETE
    FROM TestTable
    WHERE ID NOT IN
    (
    SELECT MAX(ID)
    FROM TestTable
    GROUP BY NameCol)
    GO
    -- Selecting Data
    SELECT *
    FROM TestTable
    GO
    DROP TABLE TestTable
    GO

  • #758236
    I Think this will be simplest,

    delete from EmpDup where EmpID in(select EmpID from EmpDup group by EmpId having
    count(*) >1)

    Good Luck!

    Regards,
    J.Sunil Jas
    www.thisisdotnet.blogspot.com


  • Sign In to post your comments