Reputation: 9
I need to update two table in single query.
Please find the below query.
Update
m_student_moreinfo
INNER JOIN
m_student
ON
m_student_moreinfo.studentID = m_student.id
SET m_student_moreinfo.MIAStartdate=GETDATE(),m_student.status='Clinical MIA'
where
studentID IN
(
Select
smi.studentID
FROM
dbo.m_student st
INNER JOIN
dbo.m_student_course sc
on
sc.studentID=st.id
INNER JOIN
dbo.m_student_classClinical scl
on
scl.studentcourseID=sc.id
INNER JOIN
dbo.m_student_moreinfo smi
on
smi.studentID=st.id
where
scl.startDate<=GETDATE() and scl.endDate >=GETDATE()
and MIAStartdate IS NULL
)
I am getting Incorrect syntax near Inner
.
Upvotes: 0
Views: 59
Reputation: 1030
Simple answer: You can not.
What you can do is two update queries in a transaction:
BEGIN TRANSACTION;
update query 1
update query 2
COMMIT;
That wil do the job for you
Upvotes: 0
Reputation: 9473
You can't update two tables at once, but you can link an update into an insert using OUTPUT INTO, and you can use this output as a join for the second update please see this and that for more info
So basically you can wrap this in transaction and commit after all update steps finished.
Upvotes: 1