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

nlejzf6q 于 2023-03-07 发布在其他

关注(0)|答案(4)|浏览(144)

I am having two tables

student table it contains (Student_id,school_code,name,year,...)
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-server

来源：https://stackoverflow.com/questions/12363047/how-to-update-column-in-a-table-from-another-table-based-on-condition

4条答案

按热度按时间

zaq34kh61#

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)

赞(0）回复(0）举报 2023-03-07

bprjcwpo2#

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;

赞(0）回复(0）举报 2023-03-07

4nkexdtk3#

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

赞(0）回复(0）举报 2023-03-07

dl5txlt94#

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)

赞(0）回复(0）举报 2023-03-07

我来回答

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

4条答案

相关问题

热门标签

最新问答