Wednesday, November 28, 2007

Delete Duplicate Records Without Temp Table

Good query to have in tool bag, plus don't need temporary table for large deletes.

delete bad_rows
from StrategicAccountsCRC bad_rows with(nolock)
inner join
(
select CustomerNumber, AccountType, CustomerType,
ResponsibleCRC, Customer, MIN(AutoNumber) as min_id
from
StrategicAccountsCRC with(nolock)
group by CustomerNumber, AccountType,
CustomerType, ResponsibleCRC, Customer
having count(*) > 1) good_rows
on Bad_Rows.CustomerNumber = good_rows.CustomerNumber
and
Bad_Rows.AccountType = good_rows.AccountType
and Bad_Rows.CustomerType =
good_rows.CustomerType
and Bad_Rows.ResponsibleCRC =
good_rows.ResponsibleCRC
and Bad_Rows.Customer = good_rows.Customer
and
bad_rows.AutoNumber <> good_rows.min_id

No comments: