|
|
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
Navigation:
[Reply to this message]
|