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

sql-server subquery sql-update multiple-tables
Pavi picture Pavi · Sep 11, 2012 · Viewed 54.7k times · Source

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.

Answer

Himanshu Jansari picture Himanshu Jansari · Sep 11, 2012

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)

Related questions

Nested select statement in SQL Server

Why doesn't the following work? SELECT name FROM (SELECT name FROM agentinformation) I guess my understanding of SQL is wrong, because I would have thought this would return the same thing as SELECT name FROM agentinformation Doesn't the inner select …

Read More
Difference between Subquery and Correlated Subquery

Is the following piece of SQL Query a normal query or a Correlated Subquery ?? SELECT UserID, FirstName, LastName, DOB, GFName, GLName, LoginName, LoginEffectiveDate, LoginExpiryDate, Password, Email, ReportingTo, Mobile, CommunicationPreference, IsActive FROM (SELECT row_number() OVER (ORDER BY FirstName) AS Row, …

Read More
How to do a Postgresql subquery in select clause with join in from clause like SQL Server?

I am trying to write the following query on postgresql: select name, author_id, count(1), (select count(1) from names as n2 where n2.id = n1.id and t2.author_id = t1.author_id ) from names as n1 group by name, …

Read More