468,507 Members | 1,602 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,507 developers. It's quick & easy.

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
Jul 20 '05 #1
3 3416

"M Wells" <pl**********@planetthoughtful.org> wrote in message
news:io********************************@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
Jul 20 '05 #2
On Fri, 02 Apr 2004 17:19:29 GMT, M Wells
<pl**********@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
Jul 20 '05 #3
On Fri, 2 Apr 2004 19:28:16 +0200, "Simon Hayes" <sq*@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
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
3 posts views Thread by Steven Stewart | last post: by
2 posts views Thread by Phil Stanton | last post: by
4 posts views Thread by Phil Stanton | last post: by
3 posts views Thread by Phil Stanton | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.