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

Question

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.

Jeniferjeniffer

It is because you are getting more than one value of schoo_id. And show your full error. – Kaitlin

Doesn't any of the given answers help you? – Kaitlin

i m sorry... my problem solved... thanks all of u – Jeniferjeniffer

Answer 1

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)

Kaitlin

Answer 2

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;

Scheme

you can use <other_table> in the FROM clause? neat! Is this documented somewhere? – Triacid

Answer 3

Try this query

UPDATE student SET school_code = c.school_id  
FROM student t
  INNER JOIN school c 
    ON t.school_code = c.school_code AND t.year = c.year
WHERE c.year=x

Pizzicato

Answer 4

Update Table B set column name (of table b) =x.column name (from Table A) from    
(    
Select column name from Table A a,Table B b    
where a.Column name=b.column name            
)x    
where Table b.Column name=x.Column name(of Table b)

Alignment

Why are you using old style join syntax? Also, why are you not using the two tables and columns from the Question? – Giglio