Update this Table

Question

Update this Table

abmore

Hall of Fame

Points: 3628
More actions
September 21, 2007 at 3:50 am

#174919

CREATE TABLE #tmpt1
(
Col1TINYINTNOT NULL IDENTITY(1,1),
Col2TINYINT
)
INSERT#tmpt1(Col2)
SELECT1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
DECLARE @a VARCHAR(100)
SET @a = '1, 2, 3'
UPDATE #tmpt1 SET col2 = 10 WHERE col1 in ( @a )
Now my query is, I want to update the table with search criteria provided in string format. This can be done using dynamic query but is there is any solutions without Dynamic Query..
Abhijit More.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

look in the script contributions here on SSC and find the split() function. it takes a string, and converts it to a table based on the delimiter.

select dbo.split(@a,',') would return a table witht eh values, so you can use your IN() sdtatment:

UPDATE #tmpt1 SET col2 = 10 WHERE col1 in (SELECTdbo.split(@a,',') )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

abmore Hall of Fame Points: 3628 More actions · Answer 2

thank you lowell, for your reply. but i want to do it without creating additional functions / SP.

is it possible...

Lowell SSC Guru Points: 323504 More actions · Answer 3

yes...but you have to take the same code from that function, which uses charindex and other stuff, and incorporate it into your query.

That makes it harder to read, but that's how to do it in order to do it inline. otherwise, it's dynamic SQL.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!