What's the best way to dedupe a table on the unique key? So far I can find out the number of duplicates by using the SQL below however how do I delete the duplicates leaving only 1 record for each custno (as you see below the count varies)?
select custno, count(custno) "cnt"
from customers
group by custno
having (count(custno)>1)
custno cnt
1234 2
5678 3
9101 1
1121 2
TIA.
You could select the DISTINCT rows into a new, temporary, table and then DELETE or TRUNCATE the original table, and insert the rows back from the Temporary table.
Although your CUSTNO has multiple matching rows, are the other columns in the rows identical? If not you will have to sort out which of the various duplicate CUSTNO rows is the one you want to keep - e.g. how would you decide which one has the correct / most recent ADDRESS columns (assuming, for the sake of this example, that you are storing ADDRESS in your CUSTOMERS table)
Search for Delete duplicate+sql server in google/bing
Thanks for the replies. The solution I went for was to create a new table with an index with ignore_dup_key as this allowed me to dedup on multiple coumns:
CREATE TABLE cleandup(
custno NUMERIC(9)
custname VARCHAR(10)
custaddress VARCHAR(30))
CREATE UNIQUE INDEX removeduplicates ON cleandup (custno,custname,custaddress) WITH IGNORE_DUP_KEY
INSERT INTO cleandup
SELECT custno,custname,custaddress
FROM CUSTOMER
