| Author |
 Topic  |
|
|
rtown
Yak Posting Veteran
53 Posts |
 Posted - 02/21/2013 : 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, 1990
These 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
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 02/21/2013 : 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 = QtyFromQuery
FROM
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
Flowing Fount of Yak Knowledge
USA
2875 Posts |
Posted - 02/21/2013 : 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.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 02/21/2013 : 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 = QtyFromQuery
FROM
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' |
Edited by - rtown on 02/21/2013 15:12:08 |
|
|
|
James K
Flowing Fount of Yak Knowledge
3873 Posts |
Posted - 02/21/2013 : 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 T1
SET 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 - 02/21/2013 : 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 T1
SET 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
USA
191 Posts |
Posted - 02/21/2013 : 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
Flowing Fount of Yak Knowledge
USA
2875 Posts |
Posted - 02/21/2013 : 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.
Jim
Everyday I learn something that somebody else already knew |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2886 Posts |
|
|
rtown
Yak Posting Veteran
53 Posts |
|
| |
Topic |
|
Update table ID's from array?