Author |
Topic |
rtown
Yak Posting Veteran
53 Posts |
Posted - 2013-02-21 : 14:28:25
|
Hi everyone,Unsure how to go about updating multiple records based on an array of record ID numbers.Let's say I have the following record ID number to be updated:1999, 1998, 1997, 1996, 1995, 1994, 1991, 1990These are generated by the user selecting them, the quantity is dynamic.Let's say I want to UPDATE Table1 SET Quantity='46594' WHERE ID is in the array above. How can I accomplish this? The dynamic quantity is my problem at the moment...Any help appreciated! |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-21 : 14:37:36
|
Are the ID's to be updated and the corresponding quantities obtained via a query? If it is, something like this:UPDATE t1 SET Quantity = QtyFromQueryFROM Table1 t1 INNER JOIN ( --Query that gets ids and quantities here -- e.g. select id, qty from sometable )s ON s.Id = t1.Id; |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-21 : 14:44:21
|
You should use parsing function, like Jeff Moden's DelimiterSplit8k, which will turn your delimited string in to a table that you can then join to.JimEveryday I learn something that somebody else already knew |
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2013-02-21 : 14:47:54
|
quote: Originally posted by James K Are the ID's to be updated and the corresponding quantities obtained via a query? If it is, something like this:UPDATE t1 SET Quantity = QtyFromQueryFROM Table1 t1 INNER JOIN ( --Query that gets ids and quantities here -- e.g. select id, qty from sometable )s ON s.Id = t1.Id;
No. The ID array and quantity (quantity will always be only one value) are generated from a form on another page. I can access them via variables.Basically I want the following: (obviously this will not work this way)UPDATE Table1 SET Quantity='00111' WHERE ID='2549', '2986', '2154', '2955' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-21 : 16:39:38
|
As Jim suggested, the ideal way to do this is to use a splitter function which will yield a table that contains all the IDs, to which you would join Table1.UPDATE T1SET Quantity = '00111'FROM Table1 T1 INNER JOIN dbo.DelimiterSplit8k(',',@YourStringContainingIDs) s ON s.Item = T1.ID; Another alternative is to do the following:UPDATE Table1 SET Quantity = '00111'WHERE ','+ @YourStringContainingIDs + ',' LIKE '%,'+CAST(ID AS VARCHAR(32)) + ',%'; |
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2013-02-21 : 18:24:57
|
quote: Originally posted by James K As Jim suggested, the ideal way to do this is to use a splitter function which will yield a table that contains all the IDs, to which you would join Table1.UPDATE T1SET Quantity = '00111'FROM Table1 T1 INNER JOIN dbo.DelimiterSplit8k(',',@YourStringContainingIDs) s ON s.Item = T1.ID; Another alternative is to do the following:UPDATE Table1 SET Quantity = '00111'WHERE ','+ @YourStringContainingIDs + ',' LIKE '%,'+CAST(ID AS VARCHAR(32)) + ',%';
I dont know what delimitersplit8k I have never heard of that.The second option results in an error...I guess this cant be done by simple methods. |
|
|
sanjnep
Posting Yak Master
191 Posts |
Posted - 2013-02-21 : 18:26:38
|
CREATE TABLE #Temp (ROWS VARCHAR(50)) GO DECLARE @string NVARCHAR(MAX) SET @string = '2549,2986,2154,2955' -- Put here dynamic values seperete by comma SELECT @string = ''''+REPLACE(@string,',',''' ))AS ROWS UNION ALL SELECT RTRIM(LTRIM(''')+'''' SELECT @string = 'SELECT RTRIM(LTRIM( ' + @string + ')) AS ROWS' INSERT INTO #Temp EXEC SP_EXECUTESQL @string GO UPDATE Table1 SET Quantity='00111' WHERE ID IN (SELECT ROWS FROM #Temp) GO DROP TABLE #Temp |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-02-21 : 18:43:50
|
quote: I dont know what delimitersplit8k I have never heard of that.The second option results in an error...I guess this cant be done by simple methods.
Google is a developer's best friend.JimEveryday I learn something that somebody else already knew |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2013-02-21 : 19:27:13
|
DelimitedSplit8K is here rtown: http://www.sqlservercentral.com/articles/Tally+Table/72993/ elsasoft.org |
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2013-02-21 : 20:44:34
|
quote: Originally posted by jezemine DelimitedSplit8K is here rtown: http://www.sqlservercentral.com/articles/Tally+Table/72993/ elsasoft.org
Thanks guys. I have found another way of doing this with minimal complication. I think I was approaching this problem from the wrong angle. I appreciate everyones responses! |
|
|
|