Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update table ID's from array?

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, 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
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 = 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;
Go to Top of Page

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.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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 = 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'
Go to Top of Page

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 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)) + ',%';
Go to Top of Page

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 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.
Go to Top of Page

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
Go to Top of Page

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.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

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
Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -