September 24, 2009 at 7:23 am
Hi,
I´m trying to update a table. Here is the case. I want to find a value and store it in a variable from one table. I store the 'seq' number where the column name is 'HEADER'. After this I want to take this value and use it to update another table with this 'seq' number.
So I want to return this value in some way in the next statement.
After the second statement I want to delete the variable.
DECLARE @find int
SET @find = (SELECT t1.seq FROM t1 WHERE t1.name = 'HEADER')
go
UPDATE t2.seq
SET t2.seq = @find
WHERE t2.name LIKE '%*%'
//
Regards
September 24, 2009 at 7:35 am
- SQL2005 introduced the OUTPUT clause. You could use that to do what you intend to.
- Why don't your perform a direct update of T2 ?
DECLARE @opt table (thecol int)
UPDATE T2
SET t2.seq = @t1.seq
OUTPUT INSERTED.theID
INTO @opt
from t2 T2
, (SELECT top (1) t1.seq FROM t1 WHERE t1.name = 'HEADER') T1
WHERE T2.name LIKE '%*%'
Update X
set coly = A.thecol
from tx X
, (SELECT top (1) thecol FROM @opt ) A
Where ....
Do keep in mind this trigger runs in the originating transaction ! If the trigger fails, the whole transaction fails !
Also keep in mind, having applications run in parallel or multi user, your trigger will also suffer row locks, ... that are not within its own scope.
Only use a trigger to process a minimum of data your data system strictly needs to consider an operation complete ! (handle the rest in background, out of that scope )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply