SQL Server Performance

Delete only record from Duplicate records

Discussion in 'General Developer Questions' started by sonnysingh, Sep 7, 2006.

  1. sonnysingh Member

    Hi Folks
    I need to delete only 1 row out of same duplicate rows from my table. for example same supplier exist 10 times and another 5 times. I want ot delete 1 records from each of supplier like records left after deletion e.g 9 for first supplier and 4 for second supplier..

    regards
  2. ranjitjain New Member

  3. saransh New Member

    Hi Sonny,

    Try this!! Of course u hv to mk few changes before it works on ur data structure.

    CREATE PROCEDURE proc1
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE @tempTable TABLE
    (
    col1 INT
    )

    INSERT INTO @tempTable
    SELECT COL1 FROM TABLE1 GROUP BY COL1 HAVING COUNT(COL1)>1

    DECLARE @ID INT
    DECLARE @tempWorkTable TABLE
    (
    col1 INT
    )

    SELECT TOP 1 @ID=COL1 FROM @tempTable ORDER BY 1

    WHILE EXISTS (SELECT * FROM @tempTable)
    BEGIN
    INSERT INTO @tempWorkTable
    SELECT COL1 FROM TABLE1 WHERE COL1=@ID

    DELETE TABLE1 WHERE COL1=@ID

    DECLARE @CURROWCOUNT INT
    SELECT @CURROWCOUNT=COUNT(*) FROM @tempWorkTable

    WHILE (@CURROWCOUNT>1)
    BEGIN
    INSERT INTO TABLE1
    SELECT TOP 1 COL1 FROM @tempWorkTable ORDER BY 1
    SET @CURROWCOUNT=@CURROWCOUNT-1
    END

    DELETE @tempWorkTable
    DELETE @tempTable WHERE COL1=@ID
    SELECT TOP 1 @ID=COL1 FROM @tempTable ORDER BY 1
    END
    END
    GO

    Thanks

    saransh
  4. Adriaan New Member

    DELETE FROM tbl
    WHERE EXISTS
    (SELECT * FROM tbl t1
    WHERE t1.key = tbl.key AND t1.PK < tbl.PK)

    This leaves only the row with the lowest PK for the same key value. If the table does not already have a Primary Key, then add an IDENTITY column as the PK, and then run the query.


    Slightly unusual request. If you have true duplicate entries, you would want to keep just one entry.
  5. Madhivanan Moderator

  6. sonnysingh Member

    Hi Guys... once again thanks a lot.. It is always feel good when people help other like this..
    yes ranjit... I did find more articles on it but the one find on this site is best suit my requirement.
    saransh,Adriaan...thanks for script..
    Adriaan .. yes it is unusual.. cos I want to flagged only on record out of duplicate records after shift them into temp table and delete only one record out of duplicate records in original table and shift back flagged records to the original table. so, in this way I will have original number of records in staging table as well as the single record for each combination (flagged one).. I hope you get it..

    regards
  7. FrankKalis Moderator

    So your problem is solved? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  8. shaileshk New Member

Share This Page