Dupes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

morphius - 25 Mar 2008 01:44 GMT

ID CID Date
1 1 3/10/2008
2 2 3/10/2008
3 3 6/20/2007
4 3 6/20/2007
5 4 3/10/2008
6 3 3/30/2007
7 5 6/20/2007
8 3 6/20/2007

IDs 3,4, 8 are duplicates. What would the delete statement look like to
delete IDs 4 & 8 and leave ID 3.

Reply to this Message

Tom Moreau - 25 Mar 2008 01:53 GMT

Try:

delete MyTable
where exists
(
select
*
from
MyTable m
where
m.CID = MyTable.CID
and
m.[Date] = MyTable.CID
and
m.ID > MyTable.CID
)

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Reply to this Message

morphius - 25 Mar 2008 03:59 GMT

Tom,
I modified to:
and

> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID

This works. Thanks..

> Try:
>
[quoted text clipped - 25 lines]
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.

Reply to this Message

Tom Moreau - 25 Mar 2008 12:07 GMT

Oops! God catch.

Signature

Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Tom,
I modified to:
and

> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID

This works. Thanks..

"Tom Moreau" wrote:

> Try:
>
[quoted text clipped - 25 lines]
> IDs 3,4, 8 are duplicates. What would the delete statement look like to
> delete IDs 4 & 8 and leave ID 3.

Reply to this Message

Plamen Ratchev - 25 Mar 2008 05:55 GMT

On SQL Server 2005 you can do:

;WITH Dups (seq)
AS
(SELECT ROW_NUMBER() OVER(
PARTITION BY cid, date
ORDER BY id)
FROM Foo)
DELETE Dups
WHERE seq > 1;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message