通过连接多个表更新表中的列

I have a table user I need to update,

我需要更新一个表用户，

user table contains branchId and accountName. branchId is a fk reference on table branch, which contains institutionId. InstitutionId is a FK reference to institution table. Institution table contains name and id.

用户表包含branchId和accountName。branchId是一个fk对表分支的引用，它包含机构id。机构id是指机构表的FK引用。机构表包含名称和id。

I want to update accountName in the user table to that user's equivalent institution name.

我想将用户表中的accountName更新为该用户的等效机构名称。

What I currently have is

我现在拥有的是

update [user] set accountName = 
(Select i.NAME from institution i LEFT JOIN [branch] b on b.institution_id = i.id and b.id = branchId)

but I'm getting

但我得到

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

子查询返回超过一个值。当子查询跟随=、!=、<、<=、>、>=或当子查询用作表达式时，这是不允许的。声明已被终止。

I'm using mssql.

我使用该软件。

I'm no pro using sql. Any help would be appreciated.

我不是sql专家。如有任何帮助，我们将不胜感激。

Thank you!.

谢谢你！

2 个解决方案

#1

Try this:

试试这个:

UPDATE u
SET u.accountName = i.name
FROM User u
JOIN Branch b ON b.id = u.branchId
JOIN Institution i ON i.id = b.institutionId

#2

You need to join your target table with the source. Firstly create a SELECT query

您需要将目标表与源表连接。首先创建一个SELECT查询。

Select i.NAME, u.accountName
from institution i 
JOIN [user] u
on {set suitable criteria}
LEFT JOIN [branch] b on b.institution_id = i.id and b.id = branchId

and check that this is returning the correct results. The replace the SELECT with and UPDATE as follows:

检查这是否返回了正确的结果。替换选择和更新如下:

update u set accountName = i.NAME
from institution i 
JOIN [user] u
on {set suitable criteria}
LEFT JOIN [branch] b on b.institution_id = i.id and b.id = branchId

#1