Posted by Hugo Kornelis on 07/25/06 20:14

On Tue, 25 Jul 2006 10:36:10 GMT, Maury wrote:

>Hello,
>I have a stored procedure that deletes duplicates
>in one table:
>
>....
>AS
>BEGIN
> DELETE FROM mytable
> WHERE Id IN(
> SELECT Max(id)
> from mytable
> group by date, idsens
> having count(*) >1
> )
>END
>
>sometimes it happens that I have >2 rows with duplicated values.
>How can I write a new stored procedure that delete all rows with
>duplicated infomrations (leaving only one row with those values)?

Hi Maury,

For SQL Server 2005:

WITH DeDupe (Id, rn)
AS (SELECT Id, ROW_NUMBER() OVER (PARTITION BY date, idsens ORDER BY Id)
FROM mytable)
DELETE DeDupe
WHERE rn > 1;

For all versions of SQL Server:

DELETE FROM mytable
WHERE EXISTS
(SELECT *
FROM mytable AS m2
WHERE m2.date = mytable.date
AND m2.idsens = mytable.idsens
AND m2.Id < mytable.Id);


--
Hugo Kornelis, SQL Server MVP

[Back to original message]