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