delete duplicate value

  • How to delete the duplicate value in the table..

  • I assume that this table does not have a primary key 🙂 (it is bad, but it is of course another story)

    You could build a new table with only the unique values (select distinct * from oldtable), and then replace the old table. (for replacing you may want to look at sp_rename.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Hi

    Please tell me how to delete when no primary key..

    Regards..

    karthikeyan MCAD

  • If there is a duplicate row, and you only want to delete one of them (or only a certain number if there are more), then you can do something ugly like:

    set rowcount 1

    GO

    delete from ..... where col1=value1 AND col2=vlaue2 AND ...

    GO

    set rowcount 0

    GO

    the rowcount 1 will ensure that only one row is deleted, even if there are many rows matching the where clause.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • karthikeyan (11/14/2007)


    Hi

    Please tell me how to delete when no primary key..

    Regards..

    karthikeyan MCAD

    Karthikeyan

    This is probably not the mose elegant way to do this, but it works for a job I run every night to delete dupes where all fields are the same other than the identity column, that is why I suggested you add an identity column. I have a flaw in my front end app that allows for dupes to be entered, and I have not had time to deal with yet. I would *strongly* suggest you dump your production table into a temp table to test. You will have to add all the columns you want to look at in the where clauses.

    --First create a test table

    IF OBJECT_ID('TempDB..#Test','u') IS NOT NULL

    DROP TABLE #Test

    CREATE TABLE #Test

    (

    Col1 VARCHAR(20),

    Col2 VARCHAR(20),

    Col3 VARCHAR(20),

    Col4 VARCHAR(20),

    Col5 VARCHAR(20)

    )

    GO

    --Then insert the test date, with lines 6 and 7 being dupes

    INSERT INTO #Test

    SELECT 'Blue','is','the','first','pair' UNION ALL

    SELECT 'Orange','is','the','second','pair' UNION ALL

    SELECT 'Green','is','the','third','pair' UNION ALL

    SELECT 'Brown','is','the','fourth','pair' UNION ALL

    SELECT 'Slate','is','the','fifth','pair' UNION ALL

    SELECT 'Orange','is','the','second','pair' UNION ALL

    SELECT 'Brown','is','the','fourth','pair'

    GO

    --Add an identity column to the table for use in deleting the dupes

    ALTER TABLE #test

    ADD ID INT IDENTITY(1,1)

    GO

    --Check #test first

    SELECT *

    FROM #test

    --Delete the dupes

    DELETE t1

    FROM #test t1,

    (--Select the MIN ID of the dupe records. It only stands to reason

    --these were in the table first

    SELECT

    MIN(t1.id)ID ,t1.col1,t1.col2,t1.col3,t1.col4,t1.col5

    FROM

    (--Derived table t1 selects all the dupes, based on the columns you specify

    --you would need to add any columns you want to look at as being criteria

    --for dupe records

    SELECT

    t1.*

    FROM #test t1,

    #test t2

    WHERE t1.col1 = t2.col1

    AND t1.col2 = t2.col2

    AND t1.col3 = t2.col3

    AND t1.col4 = t2.col4

    AND t1.col5 = t2.col5

    AND t1.id <> t2.id

    ) t1,

    #test t2

    WHERE t1.col1 = t2.col1

    AND t1.id <> t2.id

    GROUP BY t1.col1,t1.col2,t1.col3,t1.col4,t1.col5

    ) t2

    WHERE t1.col1 = t2.col1

    AND t1.col2 = t2.col2

    AND t1.col3 = t2.col3

    AND t1.col4 = t2.col4

    AND t1.col5 = t2.col5

    AND t1.id <> t2.id

    --Check #test after deletion

    SELECT *

    FROM #test

    Hope this helps

    Greg

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • If there is no primary key, another way is to SELECT DISTINCT into a temporary table, delete the original table and then INSERT the values back into the original table.

    K. Brian Kelley
    @kbriankelley

  • When I tried an approach (just messing around) similar to the

    col1=val1 and col2=val2....

    when one of the cols had nulls then it doesn't equate to another null so the row remains. Unless you want to get more complex and ugly by adding double checks for each col one for equality and another for IS NULL.

    I think using set-related approach is much better.

  • toniupstny (11/14/2007)


    When I tried an approach (just messing around) similar to the

    col1=val1 and col2=val2....

    when one of the cols had nulls then it doesn't equate to another null so the row remains. Unless you want to get more complex and ugly by adding double checks for each col one for equality and another for IS NULL.

    I think using set-related approach is much better.

    You can use COALESCE(col1, '') = COALESCE(val1, '') and ...

  • Oh... and if you want to keep the same identity numbers SET IDENTITY_INSERT tablename ON before doing the copy out and recopy then SET IDENTITY_INSERT ... OFF when all done.

  • -- Two methods I really like (and plagiarized from people on here).

    create table #ttemp

    (cola int not null)

    insert into #ttemp (cola) values (1)

    insert into #ttemp (cola) values (1)

    insert into #ttemp (cola) values (1)

    insert into #ttemp (cola) values (2)

    insert into #ttemp (cola) values (2)

    insert into #ttemp (cola) values (2)

    insert into #ttemp (cola) values (2)

    insert into #ttemp (cola) values (2)

    insert into #ttemp (cola) values (3)

    insert into #ttemp (cola) values (4)

    insert into #ttemp (cola) values (4)

    select * from #ttemp

    -- method 1

    delete a

    from #ttemp a

    where %%LockRes%% <> (SELECT Min(%%LockRes%%)

    FROM #ttemp t1

    where t1.cola = a.cola)

    -- method 2

    --; WITH #ttempCTE

    --AS

    --(

    -- SELECT ROW_NUMBER() OVER( PARTITION BY cola ORDER BY cola ) AS RowNumber

    -- FROM #ttemp

    --)

    --DELETE

    --FROM #ttempCTE

    --WHERE RowNumber != 1

    select * from #ttemp

    drop table #ttemp

Viewing 10 posts - 1 through 9 (of 9 total)

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