T-SQL Update/Select Query

[jendeacott]

Hi,

I have a requirement to update mutiple rows in a table if their ItemID exists in a comma delimated field eg. 23,22,21

One method have been using is to use a split user defined fucntion that returns a resultset allowing me to use "in".

When I use the code below I get the "Subquery returned more than 1 value."

DECLARE @SWAPS varchar(5000)
SELECT @SWAPS= '12,55,33'

UPDATE ItemOffer Set OfferStatus = 'void'
where ItemID in (Select convert(int, Value) from dbo.split(@SWAPS, ','))

But If I change the Update to a select it displays the three records which I effectively want to update??

eg.

select * from ItemOffer
where ItemID in (Select convert(int, Value) from dbo.split(@SWAPS, ','))

Can this method work with an update or is there a better way?

Any help would be great,
Thanks

Split UDF

CREATE FUNCTION dbo.Split
(
	@List nvarchar(2000),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(		
	Id int identity(1,1),
	Value nvarchar(100)
) 
AS  
BEGIN

While (Charindex(@SplitOn,@List)>0)
Begin

Insert Into @RtnValue (value)
Select
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
   Insert Into @RtnValue (Value)
    Select Value = ltrim(rtrim(@List))

    Return
END

Web Design Wetherby
Personal Web Design Service

 

[TysonLPrice]

What about:

UPDATE ItemOffer Set OfferStatus = 'void'
where ItemID like '%,%'

 

[bborissov]

DECLARE @SWAPS varchar(5000)
SELECT @SWAPS= '12,55,33,' -- Note the last comma

UPDATE ItemOffer Set OfferStatus = 'void'
where CHARINDEX(CAST(ItemID as varchar(20))+',' ,@SWAPS) > 0

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jendeacott]

Thanks for the suggestions guys.

bborissov: still returns the same subquery error?

TysonLprice: can you explain please?

Many thanks,

Web Design Wetherby
Personal Web Design Service

 

[TysonLPrice]

I didn't read your requirements closely enough. I thought you where looking for a comma in the ItemID to decide it was to be voided. Looking more closely I see it was the ItemID in a comma delimited column.

 

[bborissov]

What subquery? There is no subquery in my example.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[dbomrrsm]

Works for me:

create table ItemOffer (OfferStatus varchar (10),ItemID int)

insert into ItemOffer (ItemID) values (12)
insert into ItemOffer (ItemID) values (33)
insert into ItemOffer (ItemID) values (55)

select * from ItemOffer

NULL	12
NULL	33
NULL	55

DECLARE @SWAPS varchar(5000)
SELECT @SWAPS= '12,55,33'

UPDATE ItemOffer Set OfferStatus = 'void'
where ItemID in (Select convert(int, Value) from dbo.split(@SWAPS, ','))

select * from ItemOffer

void	12
void	33
void	55

drop table ItemOffer

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[jendeacott]

Thanks all, got the code working now.

Not sure what the problems was but hey ho.

Cheers,
John



Web Design Wetherby
Personal Web Design Service