delete duplicates

  • Hi Every body,

    I have one table Employees

    EmpID EmpName

    1 aaa

    1 aaa

    2 aaa

    3 bbb

    2 bbb

    Here in my table dont have primary key to that EMPID how can i eliminate duplicates in my table am using self joins but it is not working could any body help me plz really appreciate to you

    Thanks

  • A good method for deleting duplicates is using CTE.

    it is nicely explained in the msdn site. http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx

    Hope the above helps.

    Sriram

    Sriram

  • One common method is to move the data into another table (same structure as emp table)and drop the original emp table and rename the new table.

    Create table NewEmp.....

    SELECT DISTINCT EmpID,Empname INTO NewEmp

    From Emp

    Drop Emp

    sp_rename 'NewEmp' , 'Emp'

    "Keep Trying"

  • Hi Thank you for ur reply,

    in that EMPID dont have primary key is it possible that one because it is very big database plz help me

  • Here is a CTE based solution....

    ; WITH EmployeesCTE

    AS

    (

    SELECTROW_NUMBER() OVER( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RowNumber,

    EmpID, EmpName

    FROMEmployees

    )

    DELETE

    FROMEmployeesCTE

    WHERERowNumber != 1

    --Ramesh


  • if the table is small then create a new table with the distinct values and then drop the old table and rename the new table to the Old table name.

    Thanks -- Vijaya Kadiyala

    http://dotnetvj.blogspot.com

  • Here is a CTE based solution....

    ; WITH EmployeesCTE

    AS

    (

    SELECT ROW_NUMBER() OVER( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RowNumber,

    EmpID, EmpName

    FROM Employees

    )

    DELETE

    FROM EmployeesCTE

    WHERE RowNumber != 1

    --Ramesh

    This is elegant...

  • There are already a couple of solutions proposed that use a CTE. I would recommend picking a CTE solution.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • DECLARE @tbl TABLE

    (

    EMPIDINT,

    EmpNameVARCHAR(100)

    )

    INSERT INTO @tbl

    SELECT

    1, 'aaa'

    UNION ALL

    SELECT

    1, 'aaa'

    UNION ALL

    SELECT

    2, 'aaa'

    UNION ALL

    SELECT

    3, 'bbb'

    UNION ALL

    SELECT

    2, 'bbb'

    DELETE D FROM

    (

    SELECT

    ROW_NUMBER() OVER(PARTITION BY EMPID,EmpName ORDER BY EMPID) AS ROWS,

    EMPID,

    EmpName

    FROM

    @tbl

    )D

    WHERE

    ROWS> 1

    SELECT * FROM @tbl

    Regards,
    Mitesh OSwal
    +918698619998

  • Yes the CTE technique is the best to eliminate your duplicate values...so just do it!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Because you say your table is big and you don't seem to have the right indexes, you can use the following as an alternative to doing a select distinct or a row_number() over():

    Create a new table with a unique index on the colums where you don't want duplicates but use the IGNORE_DUP_KEY = ON option (see http://msdn.microsoft.com/en-us/library/ms188388.aspx). Copy all records from the old table to the new one, drop the old table and rename the new one.

    Done.

    Regards,

    Willem
    http://wschampheleer.wordpress.com[/url]

  • I have found good article on deleting duplicate record. You can see T-SQL Query To Delete Duplicate Records Among Identical Rows In A Table....

  • Can be done using temp table

    SELECT EmpID,EmpName INTO #temp

    FROM Employees

    GROUP BY EmpID,EmpName

    HAVING COUNT(*)>1

    DELETE Employees FROM Employees a INNER JOIN #temp b ON

    a.EmpID=b.EmpID

    INSERT INTO Employees(EmpID,EmpName)

    SELECT EmpID,EmpName FROM #temp

  • Can be done using temp table for your reference

    SELECT EmpID,EmpName INTO #temp

    FROM Employees

    GROUP BY EmpID,EmpName

    HAVING COUNT(*)>1

    DELETE Employees FROM Employees a INNER JOIN #temp b ON

    a.EmpID=b.EmpID

    INSERT INTO Employees(EmpID,EmpName)

    SELECT EmpID,EmpName FROM #temp

  • A simple way to delete dups that works on versions earlier than 2005 is:

    DELETE EMP

    FROM Employees EMP

    INNER JOIN

    (SELECT MIN(EmpID) AS EmpID, EmpName

    FROM Employees

    GROUP BY EmpName

    HAVING COUNT(*) > 1

    ) AS DUP ON

    EMP.EmpName = DUP.EmpName

    AND EMP.EmpID <> DUP.EmpID

    Todd Fifield

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply