Delete Duplicates in SQL2005

Hot Topic

Retrieving url parameters
Database mail not sending
2 independent Column Groups in a matrix?
SQL express on pc / svr questions
Error exporting a subreport to Excel!?
Access outside the firewall
merge agent error
SQL Transact: Subquery
Query Paging
Profiler ....duration

SQL Server

SQL Server 2005 SP1 "GetServiceuserGroup failed"
nText database type
Sub report ocassionally forcing page break when printing
Database connection error
Cant Install ReportBuilder The remote server returned an error: (401) Unauthorized.
script problem with POWER(x,y)
SP1 failure - incorrect platform
Sql2005 installation headaches
(SQL Server 2005) Instead of Delete Trigger
ODBC Destination in SSIS

Delete Duplicates in SQL2005

I have a db working well in SQL2005, I import data into the various tables with no problem. In one of the tables, I don't want duplicates based on names in a name column. Where I have more than one instance of the same name, I want to delete the entire row, not just the name itself. Do I have to do the duplicate search and delete after the import
 
So what do I do once the data is in my table Do I write and execute a script that will check the table and perform the delete if required I think that this is probably correct. I do have a very old script that I found in a Google search, but it seems so long and convoluted.
 
I'm hoping for a very simple solution, or script that will simply compare the names and then delete any extra instances greater than one i.e. leave at least one instance of every name.
 
I am new to databases, but I find it hard to believe that dealing with duplicates would be a difficult issue, surely this is an area that every DB has to contend with eventually. How would you check and delete duplicates in a table Remembering that I want to delete the entire row, not just the name in the name column. I would really appreciate any tips, urls, or sample code in this matter.
 
Tia
<woof>Tailwag</woof>

Answer this question

Delete Duplicates in SQL2005

  • dsani

    I moved this thread from Suggestions forum, as it is asking about SQL Server.

    To the moderators of this forum, please move this thread to a more appropriate forum if you think I moved this thread to a wrong forum.

    Regards,


    -chris

  • Darrell Davis

    Thanks Chris, I must have been on something when I posted the question in the wrong section, that is very poor form :-(

    Perhaps now someone will answer me :-)

    Cheers

     

  • Jason Matkowsky

    In order to remove rows from a table with duplicate names, you need to decide which one of these same-name rows not to delete.  One way is to use the primary key of the table (a temporary unique column like an IDENTITY is helpful for this purpose).

    Then you can do something like this:

    delete from myTable
    where primarykeycolumn < (
      select max(primarykeycolumn) from myTable as Tmatch
      where Tmatch.namecolumn = myTable.namecolumn
    )

    This should be done in a staging table, and your destination table should have UNIQUE  and NOT NULL constraints on the name column so you never have duplicate names in that table, if that's what you want.  ("Sorry, Mrs. Patel.  We already have a customer with your name, so you'll have to buy your car somewhere else.")

    Steve Kass
    Drew University
  • Delete Duplicates in SQL2005
Answer this question