Alex Rivera | Logout

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

Asked 2012-09-11T05:04:51.320
14

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.

sql sql-server sql-update subquery multiple-tables
Edit
Report

2 Answers

33

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)

answered 2012-09-11T05:13:41.297
3
UPDATE Master.Student
  SET school_code = sc.school_id 
FROM Master.school as sc
WHERE school_code = sc.school_code
  AND year = x
  AND st.year = x;
answered 2012-09-11T05:10:14.557

Your Answer