SQL Server Forums
Profile | Register | 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?
 New Topic  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

3695 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
2869 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

3695 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
2869 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000