Deleting Duplicate Records Made Easy in 2005

  • With ROW_NUMBER ( ) Function And using CTE the Duplicate Records Can be Eliminated Easily.

    Suppose we have the situation to delete some duplicate records in our table. Suppose consider one table

    create table #Test

    (

    EmpID int,

    EmpName varchar(50)

    )

    –Insert the Records into #Test table

    insert into #Test values(1,’Daya‘)

    insert into #Test values(1,’Daya‘)

    insert into #Test values(1,’Daya‘)

    Now i have two duplicate records inserted and i want to delete those records. The following query will delete the duplicate records

    –Query to Delete Duplicate Records

    WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )

    DELETE FROM Emp WHERE RNUM > 1

  • Note: watch out for ending previous statement with SEMICOLON

    Here's a working code sample

    CREATE TABLE #Test

    (

    EmpID INT,

    EmpName VARCHAR(50)

    )

    INSERT INTO #Test VALUES(1,'Daya')

    INSERT INTO #Test VALUES(1,'Daya')

    INSERT INTO #Test VALUES(1,'Daya')

    SELECT * FROM #Test;

    WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )

    DELETE FROM Emp WHERE RNUM > 1

    --SELECT FROM Emp WHERE RNUM > 1

    SELECT * FROM #Test

    DROP TABLE #Test

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • Delete From TableName

    Where ID Not IN

    {

    Select MAX(ID) From TableName

    Group By Col1, Col2...

    }

    By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.

    For more Interview question Click on billow link.

    http://sqlserver4us.blogspot.in/[/url]

  • weblorquins (7/16/2012)


    Delete From TableName

    Where ID Not IN

    {

    Select MAX(ID) From TableName

    Group By Col1, Col2...

    }

    By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.

    For more Interview question Click on billow link.

    http://sqlserver4us.blogspot.in/[/url]

    But this method will not be applicable if there is no id or numeric column.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • rhythmk (7/16/2012)


    weblorquins (7/16/2012)


    Delete From TableName

    Where ID Not IN

    {

    Select MAX(ID) From TableName

    Group By Col1, Col2...

    }

    By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.

    For more Interview question Click on billow link.

    http://sqlserver4us.blogspot.in/[/url]

    But this method will not be applicable if there is no id or numeric column.

    It will work ... for example

    create table #Test

    (EmpID VARCHAR(8),

    EmpName varchar(50))

    insert into #Test VALUES('abc','Daya')

    insert into #Test values('abc','Daya')

    insert into #Test values('abc','Daya')

    ;WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )

    DELETE FROM Emp WHERE RNUM > 1

    select EmpId, EmpName FROM #Test

    Result:

    abcDaya

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • bitbucket-25253 (7/16/2012)


    rhythmk (7/16/2012)


    weblorquins (7/16/2012)


    Delete From TableName

    Where ID Not IN

    {

    Select MAX(ID) From TableName

    Group By Col1, Col2...

    }

    By The help of Table name useing ID and the Group By we can delete the duplicate record from table in sql server.

    For more Interview question Click on billow link.

    http://sqlserver4us.blogspot.in/[/url]

    But this method will not be applicable if there is no id or numeric column.

    It will work ... for example

    create table #Test

    (EmpID VARCHAR(8),

    EmpName varchar(50))

    insert into #Test VALUES('abc','Daya')

    insert into #Test values('abc','Daya')

    insert into #Test values('abc','Daya')

    ;WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )

    DELETE FROM Emp WHERE RNUM > 1

    select EmpId, EmpName FROM #Test

    Result:

    abcDaya

    Hi bitbucket,

    I indicated the method posted by "weblorquins", the one used with table ID column and Group By clause. 😉

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • It is easy to delete records by considering two colmns

    create table #Test

    (EmpID VARCHAR(8),

    EmpName varchar(50))

    insert into #Test VALUES('abc','Daya')

    insert into #Test values('abc','Daya')

    insert into #Test values('def','Daya')

    ;WITH Emp AS (SELECT ROW_NUMBER ( ) OVER ( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RNUM FROM #Test )

    DELETE FROM Emp WHERE RNUM > 1

    select * FROM #Test

    Output

    EmpIDEmpName

    abcDaya

    defDaya

Viewing 7 posts - 1 through 6 (of 6 total)

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