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

delete multiple rows with 2 ids

Hello All,
I have a table:
idSurrogate int identity
id1 int
id2 int

id1 + id2 is a unique index

I need to delete multiple rows from the table given a list of id1 and a list of id2
In other words
@.id1List = '10,20,30'
@.id2List = '1,3,5'
I need to delete these 3 rows from the table
1) @.id1=10 and @.id2=1
2) @.id1=20 and @.id2=3
3) @.id1=30 and @.id2=5

I am a bit lazy today - can anyone help out with a delete sql stmt

Thanks!

You could use a TVF that splits the ids into individual values. You can then write a SELECT like:

delete your_table

where exists(select * from (

select t1.val, t2.val

from dbo.split_string(@.id1List) as t1

join dbo.split_string(@.id2List) as t2

on t2.idx = t1.idx /* index is column that gives position of value in the string */

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

See the link below for some ideas and samples on how to implement the TVF.

http://www.sommarskog.se/arrays-in-sql.html

Alternatively, you can modify your SP interface such that you pass the Ids as separate parameters. You need to however fix the maximum number of id pairs and this method is restricted to the number of parameters for a SP. You can then do something like:

delete your_table

where (id1 = @.id1_1 and id2 = @.id2_1)

or (id1 = @.id1_2 and id2 = @.id2_2)

or (id1 = @.id1_3 and id2 = @.id2_3)

...

-- or

delete your_table

where exists(select * from (

select @.id1_1, @.id2_1

union all

select @.id1_2, @.id2_2

...

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

|||Yes, that should do the trick.
I happen to have a 'List_To_Table' table value udf that includes an identity column.

Thanks for the help - I greatly appreciate it|||

Lazy...big time...I would never respond to this...but

Delete from MyTable

where ISEVEN(@.id1list) = TRUE

OR ISEVEN(@.idlist2) = FALSE

OR

Delete from MyTable

where @.id1 mod 10 = 0

OR @.id2 mod 10 <> 0

Write a UDF (User Defined Function)

Adamus

|||

Umachandar Jayachandran - MS wrote:

You could use a TVF that splits the ids into individual values. You can then write a SELECT like:

delete your_table

where exists(select * from (

select t1.val, t2.val

from dbo.split_string(@.id1List) as t1

join dbo.split_string(@.id2List) as t2

on t2.idx = t1.idx /* index is column that gives position of value in the string */

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

See the link below for some ideas and samples on how to implement the TVF.

http://www.sommarskog.se/arrays-in-sql.html

Alternatively, you can modify your SP interface such that you pass the Ids as separate parameters. You need to however fix the maximum number of id pairs and this method is restricted to the number of parameters for a SP. You can then do something like:

delete your_table

where (id1 = @.id1_1 and id2 = @.id2_1)

or (id1 = @.id1_2 and id2 = @.id2_2)

or (id1 = @.id1_3 and id2 = @.id2_3)

...

-- or

delete your_table

where exists(select * from (

select @.id1_1, @.id2_1

union all

select @.id1_2, @.id2_2

...

) as i(id1, id2)

where i.id1 = your_table.id1 and i.id2 = your_table.id2

)

WTF is this garbage T-SQL101?

Adamus

|||

Adamus Turner wrote:

Lazy...big time...I would never respond to this...but

Delete from MyTable

where ISEVEN(@.id1list) = TRUE

OR ISEVEN(@.idlist2) = FALSE

OR

Delete from MyTable

where @.id1 mod 10 = 0

OR @.id2 mod 10 <> 0

Write a UDF (User Defined Function)

Adamus

Just out of quriosity, wouldnt this be just as effective:

delete from MyTable where id1 in @.idlist1 and id2 in @.idlist2|||HPEvju,
Re: Just out of quriosity, wouldnt this be just as effective:

delete from MyTable where id1 in @.idlist1 and id2 in @.idlist2

Not quite what i need
i want to delete @.id1=10 and @.id2=1
but i don't want to delete @.id1=10 and @.id2=3

Adamus ,
?
|||

Aha, didn't quite understand what you where trying to do at first. Anyways, why not pass the input as XML (assuming you are using sql 2005 that is) and do a join?

That way you can work with the data as if it was just another table.

|||

HPEvju wrote:

Aha, didn't quite understand what you where trying to do at first. Anyways, why not pass the input as XML (assuming you are using sql 2005 that is) and do a join?

That way you can work with the data as if it was just another table.

Xml would be lovely. Unfortunately, I am writing a stored procedure to accommodate a front end that passes two lists.
In other words - the front end says " hey delete these rows for me here are two ordered lists"|||

I used to do somthing like this in sql server 2000 with this user defined function:

CREATE function splitAry( @.aryTmp as varchar(8000), @.separator as char(1) = ',')
returns @.tmp table (
ident int IDENTITY (1, 1) NOT NULL ,
string varchar(35) )
AS
BEGIN
declare @.iPos int
set @.iPos = PATINDEX('%' + @.separator + '%',@.aryTmp)
WHILE LEN(@.aryTmp) > 0 and @.iPos > 0
BEGIN
set @.iPos = PATINDEX('%' + @.separator + '%',@.aryTmp)
IF @.iPos > 0
BEGIN
insert into @.tmp values(SUBSTRING(@.aryTmp,1,@.iPos-1))
set @.aryTmp = SUBSTRING(@.aryTmp,@.iPos+1,LEN(@.aryTmp)-@.iPos)
END
END
INSERT INTO @.tmp VALUES(@.aryTmp)
RETURN
END

that way you get two tables that you can easily join against.

|||HPEvju,

After reading Umachandar Jayachandran's answer, I was put on the right track and used my own udf that essentially does the same as your splitAry.

Thanks for all your help

Delete Multiple IDs

Hi,

I am getting doubles for all my IncidentIDs in IncidentID column.

Ex:

Incident ID

13734

Incident ID

13734

Is there a delete statement to get rid of more then one Replication of the same ID?

Thanks

There are numerous ways available to achieve this

Simplest way is Copy the table Data to temporary table, delete the data in original table, select distinct data from temporary table and insert into the original table

|||isnt there a query i can use?|||

try like this

select * into #temptable from yourtable

Go

Delete from yourtable

Go

Insert yourtable

Select distinct * from #temptable

Go

Drop table #temptable

|||where did all my data go in the original table?|||

I didn't get you properly, did u executed the mock script ? what's the result?

|||

i excecuted the script you have me filling 'yourtable' as the name of my current table and leaving #temptable as is.

I cannot find any data now..

|||Can you provide me your table structure and sample data|||

Most of the table looks like this, There are close to 13,000 of these records. For some odd reason now theres 26,000 due to the double copy/replication of each record. i just want to delete the 2nd copy. After I ran your mock query everything is missing.

Columns: Sample Data:

Incident Id 1212

Description Cannot get program to run.

Resolution Double clicked wrong icon.

|||

I tested the procedure again, It is working as desired

Let me know the Exact sql statements you used