Results 1 to 6 of 6

Thread: [SQL] Elminating duplicate rows from a table

  1. #1

    Thread Starter
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,173

    [SQL] Elminating duplicate rows from a table

    How to remove all duplicate rows from a SQL Server table

    Code:
    DECLARE @iErrorVar int,
    
    @vchFirstname INT,
    
    @iReturnCode int,
    
    @vchAddress1 varchar(100),
    
    @iCount int,
    
    @chCount char(3),
    
    @nvchCommand nvarchar(4000)
    
    
    DECLARE DelDupe CURSOR FOR
    
    SELECT COUNT(*) AS Amount,
    
    liCompanyId
    
    FROM CompanyDetails
    
    GROUP BY liCompanyId
    
    HAVING COUNT(*) > 1
    
    
    OPEN DelDupe
    
    FETCH NEXT FROM DelDupe INTO @iCount,
    
    @vchFirstname
    
    WHILE (@@fetch_status = 0)
    
    BEGIN
    
    
    
    SELECT @iCount = @iCount - 1
    
    SELECT @chCount = CONVERT(char(3),@iCount)
    
    --Now we can build our dynamic ROWCOUNT and DELETE statement:
    
    SELECT @nvchCommand = N'SET ROWCOUNT ' + @chCount + ' DELETE FROM CompanyDetails WHERE liCompanyId = ' +  CONVERT(VARCHAR(20),@vchFirstname) 
    
    EXEC sp_executesql @nvchCommand
    
    FETCH NEXT FROM DelDupe INTO @iCount,
    
    @vchFirstName
    
    END
    
    CLOSE DelDupe
    
    DEALLOCATE DelDupe
    Substitute 'CompanyDetails' with your table name and liCompanyId with the primary key field of that table.

  2. #2
    Shared Member
    Join Date
    May 2005
    Location
    Kashmir, India
    Posts
    2,277

    Re: [SQL] Elminating duplicate rows from a table

    How about using a simpler version
    Code:
    Delete From 
    	TABLE1 
    	Where 
    		PKFIELD Not In (
    				Select 
    					Min(PKFIELD) 
    				From 
    					Table1 
    				Group By 
    					COLUMN1)
    Replace TABLE1 with the table name, PKFIELD with the primary Key field and Column1 with the column you are checking.
    Use [code] source code here[/code] tags when you post source code.

    My Articles

  3. #3

    Thread Starter
    I'm about to be a PowerPoster! mendhak's Avatar
    Join Date
    Feb 2002
    Location
    Ulaan Baator GooGoo: Frog
    Posts
    38,173

    Re: [SQL] Elminating duplicate rows from a table

    Won't that end up deleting nothing, since the duplicate rows have the same pkfield anyways?

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: [SQL] Elminating duplicate rows from a table

    Then it's not a true pkfield is it? The pkfield should be enforcing the uniqueness of the row.... that's what makes it the pkey..... or so I thought...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    Shared Member
    Join Date
    May 2005
    Location
    Kashmir, India
    Posts
    2,277

    Re: [SQL] Elminating duplicate rows from a table

    Quote Originally Posted by mendhak
    Won't that end up deleting nothing, since the duplicate rows have the same pkfield anyways?
    Then that Primary Key field will not be a true PK Field.

    If you have duplicate records with different primary key then this query will delete those records based on the criteria given in the Group By Clause.
    Use [code] source code here[/code] tags when you post source code.

    My Articles

  6. #6
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: [SQL] Elminating duplicate rows from a table

    This is how I do it:
    Code:
    DELETE t
    FROM TABLE1 AS t
    INNER JOIN (
    	SELECT MIN(PK_ID) AS MinPK_ID, MyDupData
    	FROM TABLE1
    	GROUP BY MyDupData
    	HAVING Count(*) > 1
    ) AS d ON t.MyDupData = d.MyDupData
    WHERE d.MinPK_ID <> t.PK_ID
    I think a join is much faster than a cursor, or "not in", though I did not test your solutions yet, but from experience I know that cursors are slow, and also "not in"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width