Run two queries with one update statement

Question

Due to database design, I have to do two update queries in one statement. I've tried to do it and look through the examples online but to no avail. I'm using SQL Server 2014 and I'm using the query for my ASP.NET MVC project.

update [sip].[StudentJournalDate] sjd 
inner join [sip].[StudentJournal] sj on sjd.AdminNo = sj.AdminNo
set  sj.WeekNo = @WeekNo,
     sjd.WeekNo = @WeekNo,
     sj.StartDateEntry = @StartDate,
     sjd,StartDateEntry = @StartDate,
     sjd.LastUpdatedBy = @LastUpdatedBy,
     sjd.LastUpdatedDate = @LastUpdatedDate,
     sjd.EndDateEntry = @EndDate 
where
    AdminNo = @AdminNo";

In SQL Server, an `update` statement can only update one table at a time. — Gordon Linoff, Oct 25 '16 at 15:30
As gordon said, probably you have something wrong with the design. — Juan Carlos Oropeza, Oct 25 '16 at 15:31
@Siyual is probably a typo. if you start your `UPDATE` with `sjd` you cant update `sj` — Juan Carlos Oropeza, Oct 25 '16 at 15:33
Possible duplicate of [SQL update query using joins](http://stackoverflow.com/questions/982919/sql-update-query-using-joins) You cant update two tables at once, and your sintaxis is wrong. — Juan Carlos Oropeza, Oct 25 '16 at 15:34
You can not update two tables in a single Query. Have to update both of the tables individually using AdminNo — Guruprakash Chinnasamy, Oct 26 '16 at 05:44
If you want this to guard against a bad state in the event of failure then you should wrap a single transaction around both of the update statements and commit at the end (*be sure to check for errors and roll back the transaction if there are any*). This will guarantee that you do not end up with 1 table with the update and another without in the event of failure. — Igor, Oct 26 '16 at 09:52

score 1 · Accepted Answer · answered Oct 26 '16 at 09:47

you should update one by one

/*Update StudentJournalDate table*/
UPDATE [sip].[StudentJournalDate]   
SET WeekNo = @WeekNo,
 StartDateEntry = @StartDate,
 LastUpdatedBy = @LastUpdatedBy,
 LastUpdatedDate = @LastUpdatedDate,
 EndDateEntry = @EndDate 
WHERE
AdminNo = @AdminNo


/*Update StudentJournal table*/
UPDATE [sip].[StudentJournal]  
SET WeekNo = @WeekNo,
 StartDateEntry = @StartDate
WHERE AdminNo = @AdminNo

score 0 · Answer 2 · edited Oct 25 '16 at 16:52

0

Juan is correct you may want to break this out into two separate update statements. You can create one update for StudentJournal and one update StudentJournalDate.

update Sj 
set sj.WeekNo = @WeekNo,
    sj.StartDateEntry = @StartDate
from [StudentJournal] Sj
where
    AdminNo = @AdminNo;

edited Oct 25 '16 at 16:52

marc_s

732,580
175
1,330
1,459

answered Oct 25 '16 at 16:40

Tywan Terrell

39
3

Run two queries with one update statement

2 Answers2