TSQL Delete Duplicates in table after comparing results found in duplicate search

Question

I have duplicate data in a single table.

Table Layout

 accountNumber | firstName | lastName | address | zip 
 SMI2365894511 | Paul      | Smith    | 1245 Rd | 89120
 SMI2365894511 | Paul      | Smith    |         |

I have the below query to find and display the duplicates.

select *
  from tableA a
  join (select accountNumber
          from tableA 
          group by accountNumber
         having count(*) > 1 ) b
    on a.accountNumber = b.accountNumber

What I would like to do is compare the results of the above query and remove the duplicate that doesn't have any address information. I'm using MS SQL Server 2014

EDIT** I have the query the way it is so can see both duplicate rows

What if one record has an address but no name and one record has a name but no address? Or if both have the same name but different addresses? Do you maintain any logging information such as inserted and modified time stamps? Or even any unique identifier (an auto-increment column) on the table? Without very solid and certain rules I'd suggest a human review of all corrections, rather than an automated system that can fail silently and cause additional corruption. *(Plus development to prevent such corruption recurring.)* — MatBailie, Aug 20 '15 at 13:56
possible duplicate of [How to delete duplicate rows in sql server?](http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — Tab Alleman, Aug 20 '15 at 14:04
I'm writing scripts to migrate data from one application to another. The idea is to sort the data initially based upon how complete the records are. The account number is generated based on first three of last name + phone number. So to get to the point this data is at I've already filterd out junk info. Now I need to filter out duplicate account numbers. Once the data has been cleaned etc it will be pushed into the other softwares database that has unique identifies in place. — Paul Walker, Aug 20 '15 at 14:06

Mark · Accepted Answer · 2015-08-20T14:08:48.453

1

delete a 
  from XmaCustomerDetails a
  join ( select accountNumber
           from XmaCustomerDetails 
          group by accountNumber
         having count(*) > 1 ) b
    on a.accountNumber = b.accountNumber
WHERE address is null

edited Aug 20 '15 at 14:08

answered Aug 20 '15 at 13:55

Mark

1,544
1
14
26

Thanks, sometimes the simplest of things are the hardest to figure out :) – Paul Walker Aug 20 '15 at 14:13

TSQL Delete Duplicates in table after comparing results found in duplicate search

1 Answers1