Update two tables at once on a Join

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
more ▼

asked Jun 08 '11 at 07:15 AM in Default

Shivu gravatar image

111 7 9 10

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

You could wrap both statements in a single transaction

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

answered Jun 08 '11 at 07:19 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

Thanks Kev! Guess wrapping them in a transaction is what I'll do.
Jun 08 '11 at 09:18 AM Shivu
(comments are locked)
10|1200 characters needed characters left

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][1] help in the situation you're experiencing?

[1]: http://msdn.microsoft.com/en-us/library/ms177564.aspx
more ▼

answered Jun 08 '11 at 07:53 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.6k 19 21 74

Thank you @Grant!
Jun 08 '11 at 09:19 AM Shivu
(comments are locked)
10|1200 characters needed characters left

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][1] setting and a nice example provided there.

[1]: http://ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/cbcaa433-58f2-4dc3-a077-27273bef65b5.htm
more ▼

answered Jun 08 '11 at 10:59 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.2k 8 11 21

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 08 '11 at 07:15 AM

Seen: 1889 times

Last Updated: Jun 08 '11 at 07:17 AM