Update two tables at once on a Join

Question

Is there a better way to do this? t1 is a table on the database and t2 is a temp table.

Update t1
Set t1_Count = t1_Count + 1
From t1
Inner Join  #t2 on t1.xy = #t2.xy
Update #t2
Set abc = 'Y'
From t1
Inner Join #t2 on t1.xy = #t2.xy

Answer 1

0

You could wrap both statements in a single transaction

begin tran
 update.....
 update.....
commit

How do you want to make it 'better'? Atomic? Consistent? Isolated? Durable? Hmm... sounds like a transaction......

answered Jun 08 '11 at 07:19 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

Thanks Kev! Guess wrapping them in a transaction is what I'll do.

Jun 08 '11 at 09:18 AM Shivu

(comments are locked)

Answer 2

0

Kev is right, you can wrap them in a transaction in order to make them part of a single unit, but no matter what, you're going to modify one table and then the other. There is no way to completely achieve a perfectly parallel operation from within a query. You must have individual UPDATE/INSERT/DELETE statements for each table.

Would the OUTPUT clause help in the situation you're experiencing?

answered Jun 08 '11 at 07:53 AM

Grant Fritchey ♦♦
62.6k ● 12 ● 20 ● 66

Thank you @Grant!

Jun 08 '11 at 09:19 AM Shivu

(comments are locked)

Answer 3

0

I only add to @Kev. When you will wrap two update statements into a transaction, take care about the XACT_ABORT settings. It should be set to ON to be sure, that the whole transaction is rolled back whenever any of the statements fail and you will not handling exceptions using TRY..CATCH.

Tale a look on MSDN for SET XACT_ABORT setting and a nice example provided there.

answered Jun 08 '11 at 10:59 AM

Pavel Pawlowski
20.3k ● 5 ● 10 ● 20

(comments are locked)

Update two tables at once on a Join

Follow this question

Related Questions