Delete record based on existence of another record in same table?

Hi All,

I have a table in SQL Server 2000 that contains several million member
ids. Some of these member ids are duplicated in the table, and each
record is tagged with a 1 or a 2 in [recsrc] to indicate where they
came from.

I want to remove all member ids records from the table that have a
recsrc of 1 where the same member id also exists in the table with a
recsrc of 2.

So, if the member id has a recsrc of 1, and no other record exists in
the table with the same member id and a recsrc of 2, I want it left
untouched.

So, in a theortetical dataset of member id and recsrc:

0001, 1
0002, 2
0001, 2
0003, 1
0004, 2

I am looking to only delete the first record, because it has a recsrc
of 1 and there is another record in the table with the same member id
and a recsrc of 2.

I'd very much appreciate it if someone could help me achieve this!

Much warmth,

Murray"M Wells" <planetquirky@.planetthoughtful.org> wrote in message
news:io7r60hup1ajlnu3je9onmb7ki2dtkmni3@.4ax.com...
> Hi All,
> I have a table in SQL Server 2000 that contains several million member
> ids. Some of these member ids are duplicated in the table, and each
> record is tagged with a 1 or a 2 in [recsrc] to indicate where they
> came from.
> I want to remove all member ids records from the table that have a
> recsrc of 1 where the same member id also exists in the table with a
> recsrc of 2.
> So, if the member id has a recsrc of 1, and no other record exists in
> the table with the same member id and a recsrc of 2, I want it left
> untouched.
> So, in a theortetical dataset of member id and recsrc:
> 0001, 1
> 0002, 2
> 0001, 2
> 0003, 1
> 0004, 2
> I am looking to only delete the first record, because it has a recsrc
> of 1 and there is another record in the table with the same member id
> and a recsrc of 2.
> I'd very much appreciate it if someone could help me achieve this!
> Much warmth,
> Murray

I think this is what you're looking for:

delete from dbo.MyTable
where recsrc = 1
and exists (
select * from dbo.MyTable m2
where MyTable.MemberID = m2.MemberID
and m2.recsrc = 2)

Simon|||On Fri, 02 Apr 2004 17:19:29 GMT, M Wells
<planetquirky@.planetthoughtful.org> wrote:

And just to show that I am trying, I attempted:

delete from #mw_dupetest as md where recsrc = 1 and exists (select mid
from #mw_dupetest where mid = md.mid and recsrc = 2)

This is obviously wrong, since I can't seem to assign a table alias in
a delete statement and I can't think of any other way of referring to
the mid column in the exists statement.

So, I'm hoping somone can help me understand how to do this the right
way.

Much warmth,

Murray|||On Fri, 2 Apr 2004 19:28:16 +0200, "Simon Hayes" <sql@.hayes.ch> wrote:

>> Murray
>I think this is what you're looking for:
>delete from dbo.MyTable
>where recsrc = 1
>and exists (
>select * from dbo.MyTable m2
>where MyTable.MemberID = m2.MemberID
>and m2.recsrc = 2)

Hi Simon,

Thank you for this! Seems like I was somewhat on the right track, I
just fudged on attempting to alias the table in the delete statement.

Thanks again!

Much warmth,

Murray