CODEWITHSUNDEEP
sql-server
Chethan
Chethan

Reputation: 9

I need to update two table in single query

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

Answers (2)

Ravenix
Ravenix

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

profesor79
profesor79

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

Related Questions