CODEWITHSUNDEEP
sqlsql-server
Luke Makk
Luke Makk

Reputation: 1197

How to declare variable in sql update statement

how to update with a new variable

let's say I wanted to do the following

update T
set T.property1 = (declare @temp varch(20)
                           @temp = 'testing')
from #temp_table_name T

is this possible. I need to update a table but the new element is the end result of a series of complicated statements and it would be a lot easier to define some variables along the way to handle intermediate outputs. What is the correct syntax for what I'm trying to do above because it's not working

Upvotes: 1

Views: 15090

Answers (2)

Matt Grande
Matt Grande

Reputation: 12157

Is something like this what you're looking for?

DECLARE @temp varchar(20)
SET @temp = 'testing, or the result of a query maybe?'

UPDATE T SET T.property1 = @temp 
FROM #temp_table_name T
WHERE 1 = 1

Upvotes: 5

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

Move all of those statements into a scalar valued user-defined function and then in your update statement do this:

update T
set T.property1 = dbo.myUdf(...)
from #temp_table_name T

where ... are any parameters it may need from the row to do its job.

Upvotes: 3

Related Questions