Removing Records from a table via SQL Stmt

[mdr227]

I have a table that has the fiels ID and SPID where SPID is the person's spouse ID. When I run a program and the user wants to eliminate one of the spoues records so that a couple only shows up one time in the result set I usually use a cursor that runs through and deletes the second spouse's record. However, I want to be able to do this with one or two SQL statements as the cursor takes quite a while to run.

 

[foxdev]

See if this fits your needs:

[tt]select id from MyTable where id not in
(select spid from MyTable)[/tt]

You'll either get what you want, or zero records; I'm not able to create a test table at the moment.

 

[mdr227]

Unfortunately, it doesn't quite do what I am looking for. If you use the code below to create a simple table what the resulting set should be is to have a record for ID #s 123, def, and 123a. The ones for 234 and abc would be eliminated because their spouse is already in the selection. Does that make sense?

create table #temp1 (
id char(10) null,
spid char(10) null
)
go

insert into #temp1 values ('123', '234')
insert into #temp1 values ('234', '123')
insert into #temp1 values ('def', 'abc')
insert into #temp1 values ('abc', 'def')
insert into #temp1 values ('123a', '')
go

 

[shyamal]

Here is a shot at it, I am not sure whether this is what you are looking for but you may have to tweak it:

delete from #temp1
where id in (select count(*)
from #temp1 as t2
where t2.id = t2.spid)>0

So when there is a count greater than 0, that is there is a duplicate in the spid remove the id.

for the first occurrence of 123 in the spid column, the id should be removed. Like i said you will have to play around with this.

 

[MalcolmW]

I always assume the worst...
What if abc has a secret life, or a second marriage, or
(perish the thought) dirty data? Or is this just a mailing list, and the extra stamp isn't worth the trouble?
insert into #temp1 values ('def', 'abc')
insert into #temp1 values ('abc', 'zzz')