SQL Server 2008 De-duping

Long story sho开发者_运维知识库rt, I took over a project and a table in the database is in serious need of de-duping. The table looks like this:

supply_req_id | int      | [primary key]
supply_req_dt | datetime | 
request_id    | int      | [foreign key]
supply_id     | int      | [foreign key]
is_disabled   | bit      |

The duplication is exists with records having the same request_id and supply_id. I'd like to find a best practice way to de-dupe this table.

[EDIT]

@Kirk_Broadhurst, thanks for the question. Since supply_req_id is not referenced anywhere else, I would answer by saying keep the first, delete any subsequent occurances.

Happy Holidays

This creates a rank for each row in the (supply_req_dt, request_id) grouping, starting with 1 = lowest supply_req_id. Any dupe has a value > 1

;WITH cDupes AS
(
    SELECT
     supply_req_id,
     ROW_NUMBER() OVER (PARTITION BY supply_req_dt, request_id ORDER BY supply_req_id) AS RowNum
    FROM
     MyTable
)
DELETE
   cDupes
WHERE
   RowNum > 1

Then add a unique constraint or INDEX

CREATE UNIQUE INDEX IXU_NoDupes ON MyTable (supply_req_dt, request_id)

Seems like there should be a command for this, but maybe that's because I'm used to a different database server. Here's the relevant support doc:

How to remove duplicate rows from a table in SQL Server http://support.microsoft.com/kb/139444

You need to clarify your rule for determining which record to keep in the case of a 'match' - the most recent, the earliest, the one that has is_disabled true, or false?

Once you've identified that rule, the rest is fairly simple:

1. select the records you want to keep - the distinct records
2. join back to the original table to get the ids for those records.
3. delete everthing where not in the joined dataset.

So let's say you want to keep the most recent records of any 'duplicate' pair. Your query would look like this:

DELETE FROM [table] WHERE supply_req_id NOT IN
(SELECT supply_req_id from [table] t 
INNER JOIN
    (SELECT MAX(supply_req_dt) dt, request_id, supply_id 
    FROM [table] 
    GROUP BY request_id, supply_id) d
ON t.supply_req_dt = d.dt
AND t.request_id = d.request_id 
AND t.supply_id = d.supply_id)

The catch is that if the supply_req_dt is also duplicated, then you'll be keeping both of the duplicates. The fix is to do another group by and select the top id

select MAX(supply_req_id), supply_req_dt, request_id, supply_id 
group by supply_req_dt, request_id, supply_id 

as an interim step. But if you don't need to do that, don't bother with it.