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

Question

Possible Duplicate:
How to update two tables in one statement in SQL Server 2005?

I have two tables and need to update one table and take effect to the other table.

These two tables are linked by a relationship

t1.col1 PK , t2.col1 FK   

t1                t2
_____________  _____________
|col1|  col2|  |col1 |col2 |            
|----|------|  |-----|-----|                             
|1   |  a   |  | 1   | d   |            
|2   |  b   |  | 2   | e   |            
|3   |  c   |  | 3   | f   |            
|____|______|  |_____|_____|

How I can update these two tables in one query in SQL Server 2008 ?

I want to do something like that

 Update College 
 Inner Join Class ON College.CollegeId = Class.CollegeId 
 set College.CollegeId = '33333333-3333-3333-3333-333333333333',
     Class.CollegeId = '33333333-3333-3333-3333-333333333333' 
 where 
     College.CollegeId = '071887ea-3c93-40ce-a112-3b849d352064'

but I get an error:

incorrect syntax near the keyword "Inner"

Agree with @AlanKuras. There is no difference in SQL 2008 and 2005 in this matter — Preet Sangha, Jun 04 '12 at 08:20
begin transaction, udpate table1, update table2, commit. easier, more mantainable, less error prone — Alex, Jun 04 '12 at 08:47
Whow, you could make sure your error message is related to the code you give at least. Voting to close - please take more care how you ask questions. — TomTom, Jun 04 '12 at 09:42
Romay_0987: CHK NOW I HAVE EDIT According to Sql server 2008 syntax — Nikhil D, Jun 04 '12 at 09:52

Nikhil D · Answer 1 · 2012-06-04T09:39:51.270

2

You achieve like this:
you can use a transaction two make sure that two UPDATE statements are treated correct.

BEGIN TRANSACTION

UPDATE Table1
  SET Table1.col1= 'XXXX' 
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = '010008'

UPDATE Table2
SET Table2.col1 = 'XXXX'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = '010008'

COMMIT

edited Jun 04 '12 at 09:39

answered Jun 04 '12 at 08:28

Nikhil D

2,479
3
21
41

I guess someone should have read the Transact-SQL syntax for the UPDATE command! – ta.speot.is Jun 04 '12 at 09:15
Don't update the primary key. It could cause a lot of problems for you keeping your data intact, if you have any other tables referencing it. Ideally, if you want a unique field that is updateble, create a new field. – Nikhil D Jun 04 '12 at 10:05

score 2 · Accepted Answer · edited May 01 '20 at 12:05

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.

Pleasure mate, Please rate the answer or mark it as the accepted answer if it helped. Thanks J — Joachim Prinsloo, Jun 04 '12 at 11:11

Ravi Vanapalli · Answer 3 · 2012-06-04T08:31:10.520

-2

You can do something like below

 UPDATE T1 INNER JOIN T2 ON T1.COL1 = T2.COL1 SET T1.COL2 = 'X', T2.COL2= 'Y'
 WHERE T1.COL1 = 2

Happy coding!!!

edited Jun 04 '12 at 08:31

answered Jun 04 '12 at 08:22

Ravi Vanapalli

9,805
3
33
43

Yes this works, I have used it in many places in previous projects – Ravi Vanapalli Jun 04 '12 at 08:50
it's not work , incorrect syntax near the keyword "Inner" i want to do something like that Update College Inner Join Class ON College.CollegeId = Class.CollegeId set College.CollegeId='33333333-3333-3333-3333-333333333333', Class.CollegeId='33333333-3333-3333-3333-333333333333' where College.CollegeId='071887ea-3c93-40ce-a112-3b849d352064' – Romay_0987 Jun 04 '12 at 08:53
This will always fail as you are updating the Primary/ Foreign key which is used for relationship to update tables. – Ravi Vanapalli Jun 04 '12 at 09:14
1

I guess someone should have read the Transact-SQL syntax for the UPDATE command! – ta.speot.is Jun 04 '12 at 09:14

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

3 Answers3