用其他表的结果更新表 [英] update a table with results from other table

问题描述

嗨 我有一张表格，其中包含一组学生数据(学生)和一张新表格(申请人).这些表中的列为:

学生:
Stud_Id
Stud_Name
Institute_Id

申请者:
App_Id
App_Name
Inst_Id

Stud_Id和App_Id是相同的数据集.我现在要做的是更新Applicants表，我想使用Students.Stud_Id = Applicants.App_Id将Institute_Id插入Inst_Id.

我尝试了一下.但是错误出来了.我该如何解决?

Hi I have a table which consists of a set of students data(Students) and a new table (Applicants). The columns in those tables are:

Students:
Stud_Id
Stud_Name
Institute_Id

Applicants:
App_Id
App_Name
Inst_Id

Stud_Id and App_Id are the same dataset. What I am going to do now is i want to update Applicants table, which I want to insert Institute_Id into Inst_Id using Students.Stud_Id = Applicants.App_Id.

I have tried a little. But error came out. How can i fix this?

UPDATE a SET a.Inst_Id = (select s.Institute_Id from Applicants a inner join Students s on a.App_Id = s.Stud_Id)
FROM Applicants a
INNER JOIN Students s ON a.App_Id = s.Stud_Id

推荐答案

没有看到所有表的示例数据在查询中，我会看一下select语句，因为我认为它会返回更多多于一个结果.

您也许还可以将查询更改为此(但是尚未经过测试！)

Without seeing sample data for all tables In the query I would look at the select statement as I believe that it is returning more than one result.

You might be able to change your query to this also (but this has''nt been tested!)

UPDATE a SET a.Inst_Id = s.Institute_Id 
FROM Applicants a
INNER JOIN Students s ON a.App_Id = s.Stud_Id

这篇关于用其他表的结果更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！