update table with split function

[jrsreenivas]

hi Friends i have a problem i have a two tables 1.Customer 2,Purchases customer table has [ Customer_id(int),name(varchar)] purchases table has [ purchase_no(int)(identity),Customer_id(int),Delivered(bool)] i will all the custmor_id's in string format and i want to update the delivered field in the purchases table with bool =1 and customer_id with split function i am using this query - where fnSlplit is spliting the customer_id into table format but not updating the purchases table UPDATE purchases SET Delivered= 1 INNER JOIN dbo.fnSplit(@CustomerList,',') AS S ON REDEMPTION_CODE = S.ID WHERE REDEMPTION_CODE = S.ID please help to solve this problem.

[leckey 0]

I could be wrong, but I think you need to do the split function first, store that in a variable, and then use that variable for the update. You have S.ID in the inner join and in the where clause--you need one or the other. I have not tried this, but something along these lines: DECLARE @newCustID VARCHAR(10) SET @newCustID = dbo.fnSplit(@CustomerList,',') UPDATE purchases SET Delivered = 1 WHERE REDEMPTION_CODE = @newCustID Hope it helps.

Back in the blog beatch! http://CraptasticNation.blogspot.com/[^]

[Robin_Roy]

Which Version of SQL Server are you using. In SQL Server 2008, you can pass Table Value Parameter to your stored procedure and in such a case no need to pass delimited string of ID's. If not, you need to create a Table Variable and store the IDs in the Table Variable first using the Split UDF and then use the same in the statement. Also you can iterate through your table of IDs and then update the purchases table.