January 25, 2008 at 8:55 am
I have a strange scenario where i am updating data in 2 tables. there is a dependency in these 2 tables in such a way that once teh 1st tabel gets updated with some new value the next update followed updates teh2nd table doinga join on the 1st table (with old value) something as in ex.
update tablea
set col1 = 'xx' (here col1 was 'test' before)
from tablea a, table b
where a.id = b.id
and a.id = 111
update tableb
set col2 = 'yy'
from tableb b, tablea a
where b.col1 = a.col1 (value o col1 should be test and not xx
and b.id = a.id
Is that something doable in straight sql or do i need to store the @old and @new values in variables to get this working.
If we have the 1st sql in a transaction and update is done is it ina commited state unless you explicitely say commit transaction. Has anyone experience this situation before. any help on this will be appreciated. TIA
January 25, 2008 at 11:00 am
If you use an OUTPUT clause in your first update, you can return the "Deleted" and "Inserted" versions of the records affected by the first update.
Return this into a table variable or a temp table and instead of joining to the first table in the second update, join to this temporary table.
Look at the OUTPUT clause in books online to get the syntax - it is pretty easy to use.
January 25, 2008 at 11:02 am
Do you have to update it the other table all the time when the first one gets updated? Then use a trigger. That is easier.
-Roy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply