0

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.

Jérémie B
  • 10,611
  • 1
  • 26
  • 43
Chethan
  • 9
  • 4
  • You can not update two tables in a single query. You will have to do it in two update statements. – TT. Feb 22 '16 at 10:00
  • that error is from the inner join next to the update. It is not possible to update 2 tables in one statement. – Kim Feb 22 '16 at 10:30

2 Answers2

1

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.

Community
  • 1
  • 1
profesor79
  • 9,213
  • 3
  • 31
  • 52
0

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

Ravenix
  • 1,010
  • 4
  • 15
  • 40
  • 1
    why not ? what is your constraint ? – Squirrel Feb 22 '16 at 09:44
  • Take a look at this thread: [Update a table using join in SQL Server](http://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server). It might put some light on your problem of using a JOIN in an update query. – Ravenix Feb 22 '16 at 10:06