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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update table ID's from array?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rtown
Yak Posting Veteran

53 Posts

Posted - 02/21/2013 :  14:28:25  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2875 Posts

Posted - 02/21/2013 :  14:44:21  Show Profile  Reply with Quote
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 - 02/21/2013 :  14:47:54  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 02/21/2013 :  16:39:38  Show Profile  Reply with Quote
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 - 02/21/2013 :  18:24:57  Show Profile  Reply with Quote
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

USA
191 Posts

Posted - 02/21/2013 :  18:26:38  Show Profile  Visit sanjnep's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2875 Posts

Posted - 02/21/2013 :  18:43:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 02/21/2013 :  19:27:13  Show Profile  Visit jezemine's Homepage  Reply with Quote
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 - 02/21/2013 :  20:44:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000