How to Update Two Tables in One Statement in SQL Server 2005

How to update multiple tables with single query

Here is an example using the output clause:

declare @ids table (id int);

update table1
    set status = 1
    output inserted.id into @ids
    where status = 2;

update table2
    set status = 1,
        date = getdate()
    where personid in (select id from @ids);

Update two tables in a single query

You can't update multiple tables in one statement, however, you can use a transaction to make sure that two UPDATE statements are treated atomically. You can also batch them to avoid a round trip.

Update multiple tables in SQL Server using INNER JOIN

You can't update more that one table in a single statement, however the error message you get is because of the aliases, you could try this :

BEGIN TRANSACTION

update A
set A.ORG_NAME =  @ORG_NAME
from table1 A inner join table2 B
on B.ORG_ID = A.ORG_ID
and A.ORG_ID = @ORG_ID

update B
set B.REF_NAME = @REF_NAME
from table2 B inner join table1 A
    on B.ORG_ID = A.ORG_ID
    and A.ORG_ID = @ORG_ID

COMMIT

Update 2 tables in one statement (MS SQL)

Syntax fix to begin with, might solve the problem as well, untested!

update db
set 
db.HeaderBgColour = @value,
o.Saved = '0'

from tbl_calendardatebox db
inner join tbl_calendarobjects o on
db.ObjectId = o.Id

where o.PageId = @page
and o.GroupField = @group and o.GroupField <> '-1'
and o.Visible = '1'
and o.CanUserEdit = '1'
and db.HeaderBgColour <> @value

Actual answer found on stackoverflow: How to update two tables in one statement in SQL Server 2005?

This is NOT possible Sadly.

How can I update two tables by single query in SQL Server 2008

Whether you update 2 tables with one statement in one query on one connection or 2 tables with two statements in one query on one connection, it is the same thing really.

You can set parameters to pass to the script then you only have to send the value once and run the script once which will in turn only use one connection.

declare @OldCollegeID
declare @NewCollegeID

Update Class set CollegeID = @NewCollegeID
where CollegeID = @OldCollegeID

Update College set CollegeID = @NewCollegeID
where CollegeID = @OldCollegeID

However, I am guessing the reason you want to do this simultaniously is because you can't update college first without updating class because of the relationship? Trying to Update College.CollegeID will result in error because of the foreign key Class.CollegeID. The same applies visa versa. Class.CollegeID will not be updatable unless the Class.CollegeID you are updating to exists in College.CollegeID. In this case I will suggest the following:

Create Procedure UpdateCollegeID
(
   declare @OldCollegeID varchar(100),
   declare @NewCollegeID varchar(100)
)

as

declare @CollegeName varchar(100)
declare @ClassName varchar(100)

set @CollegeName = (select CollegeName from College where CollegeID = @OldCollegeID)
set @ClassName = (select ClassName from Class where CollegeID = @OldCollegeID)

Insert into College (CollegeID, CollegeName)
Values(@NewCollegeID, @NewCollegeName)

Insert into Class (ClassID, CollegeID)
Values(@ClassID, @NewCollegeID, @ClassName)

Delete from Class where CollegeID = @OldCollegeID
Delete from College where CollegeID = @OldCollegeID

This will update all your old college records with the new id and you wont have to worry. You might have to do something differently in your front end application to cater for this depending on what it is. Keeping it a stored procedure will allow you to exec it and everything will update to the parameters passed.

---

Related Topics