Deleteing Duplicates

Hi,
Does anyone have a useful way of deleting duplicates so that it leaves 1 in
the table and removes the other. Currently I use a #temp table but was just
wondering if there is something slicker.
Thanks
Steve LloydDepends, assuming you have a primary key:
DELETE FROM YourTable
WHERE EXISTS
(SELECT *
FROM YourTable AS T
WHERE col1 = YourTable.col1
AND col2 = YourTable.col2
AND ... etc
AND key_col < YourTable.key_col)
If you don't have a key at all then SELECT DISTINCT into a new table
and add the key... and don't create tables without keys in future!
David Portas
SQL Server MVP
--|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
How to Identify and Delete Duplicate SQL Server Records
http://www.sql-server-performance.c..._duplicates.asp
AMB
"Steve Lloyd" wrote:

> Hi,
> Does anyone have a useful way of deleting duplicates so that it leaves 1 i
n
> the table and removes the other. Currently I use a #temp table but was ju
st
> wondering if there is something slicker.
> Thanks
> Steve Lloyd
>
>