Updating Multiple Tables
Hi All, Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.
April 27th, 2015 1:17am

An update statement can only update one table at a time.  Can you describe what you need to do or why you need to update multiple tables at once? 

Perhaps what you require is multiple update statements wrapped in a single transaction so that you can be sure all of the updates occur together or none of them? 

Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 1:24am

 Any modification including UPDATE, INSERT or DELETE must reference column from one base table. So, you can't update multiple tables in one single query. However, you can use Transaction to execute them in one batch; also you can use output clause if you want to get the updated values.

Begin Transaction

Update Query 1 

Update Query 2

Commit Transaction 
Hope this will help


April 27th, 2015 1:38am

As alternative you can write something like that 

BEGIN TRANSACTION
BEGIN TRY

Update

Update 

Update

COMMIT
END TRY
BEGIN CATCH

IF @@TRANCOUNT> 0 ROLLBACK
END CATCH;

Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 1:38am

Actually I have three table named Parts, Catalogue and Stock. If I update part quantity and supplier in parts table, those 2 information needs to be updated in catalogue and stock table also. I am not allowed to write any triggers.
April 27th, 2015 1:51am

So, write a stored procedure where you put all that logic in and wrap in BEGIN TRAN ... 
Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 1:53am

If you can, redesign your database so that there is one, and only one, truth. Here you have three tables, no one knows which one is "correct".  If you need to have the quantity in all three, create a view that joins the tables together.
April 27th, 2015 2:48pm

Hi All, Is there any way to update multiple tables in a single query. I know we can write triggers. Apart from triggers, is there any other way available in SQL Server. I am using 2008R2.

A query is not a statement; queries do not change data and statements can. TRIGGERs are procedural code, so good SQL programmers  hate them -- the way that Vegans hate eating  babies (so wrong , so many ways). 

In a well designed schema, we have one fact, one way, one time. This means that with DRI actions and REFERENCES constraints we will never have to do what you want to do! But you posted no DDL, no data and nothing to help us educate you.

Want to try again and be specific? 

Free Windows Admin Tool Kit Click here and download it now
April 27th, 2015 4:06pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics