Dupes

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.
Try:
delete MyTable
where exists
(
select
*
from
MyTable m
where
m.CID = MyTable.CID
and
m.[Date] = MyTable.CID
and
m.ID > MyTable.CID
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
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.
|||Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:

> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> 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.
>
|||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
|||Oops! God catch.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:D180E323-D5DE-4B45-A9D8-156EDC31FED4@.microsoft.com...
Tom,
I modified to:
and
> m.[Date] = MyTable.[Date]
> and
> m.ID > MyTable.ID
This works. Thanks..
"Tom Moreau" wrote:

> Try:
> delete MyTable
> where exists
> (
> select
> *
> from
> MyTable m
> where
> m.CID = MyTable.CID
> and
> m.[Date] = MyTable.CID
> and
> m.ID > MyTable.CID
> )
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "morphius" <morphius@.discussions.microsoft.com> wrote in message
> news:10594163-638B-4CF9-9FB0-7718251D47A0@.microsoft.com...
> 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.
>