sqlsql-serversql-updatesubquerymultiple-tables

How to update column in a table from another table based on condition?


I am having two tables

  1. student table it contains (Student_id,school_code,name,year,...)
  2. school table it contains (school_id,School_code,School_name,year etc.....)

I want to update the school_code column in the student table with the school_id column in the school code table based on school code and year. i m having five years data. so school_id varies for every year.

My query was

UPDATE Master.Student
   SET school_code=( select school_id from Master.school as sc
  JOIN master.student as st
    ON st.school_code=sc.school_code
 WHERE sc.year=x)
 WHERE st.year=x;

But its not updating. I am getting error of subquery returns more than one value.


Solution

  • Why to use sub-query when you can do that directly?

    UPDATE st
      SET st.school_code = sc.school_id 
    FROM master.student AS st
      JOIN Master.school AS sc
    ON st.school_code = sc.school_code
    WHERE sc.year=x
      AND st.year=x;
    

    For more info See UPDATE (Transact-SQL)