Update a column in a table by joining multiple tables

Question

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.

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

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.

Thank you!.

Well....the error is pretty clear. Your subquery is returning more than 1 row so it doesn't know which value to use. You need something to match the user with. Like an institution_id perhaps. — Sean Lange, Jun 07 '18 at 15:33
yeah that's why I did and `b.id = branchId`, `branchId` comes from the `user` table — Ayo K, Jun 07 '18 at 15:36
look this URL for help https://stackoverflow.com/questions/1604091/update-a-table-using-join-in-sql-server — Tony Dong, Jun 07 '18 at 15:37
My question is why? User will simply replicate institution. If instead user also had an individuals user name / id then adding the institution id would make sense then to get the name of the institution you would just need select u.name, i.name from user u, institution i where u.institution = i.id. This prevents data duplication. Which can cause problems. — Danimal, Jun 07 '18 at 15:40
@Danimal table structure is more complex than u think. I just reduced the scope to ask my question — Ayo K, Jun 07 '18 at 15:43
An institution has many branches. Equality only works on one record. What are you trying to do anyway??? Don't just post broken query, and expect people to understand what you are trying to do. Tell what you are trying to do, then post the broken query. That way people at least understand what you are trying to do. — Eric, Jun 07 '18 at 15:45
@Eric I'm simply trying to update all user account names to their institution name and the only way to get to a user's institution is through their branches which hold the institution id — Ayo K, Jun 07 '18 at 15:53
@Ayo K, that is what I had hoped, but you never can be sure here on the stack. It seems b.id might have duplicates or b.institution_id might which is what may be causing the issue. You could try adding Top 1 (i.NAME ) if there is no chance that there could be duplicate names. Either that or you might want to look at the tables more closely to ensure the 1 to many relationship between your primary and foreign keys. — Danimal, Jun 07 '18 at 15:53

score 2 · Accepted Answer · answered Jun 07 '18 at 15:58

2

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

answered Jun 07 '18 at 15:58

Eric

3,165
1
19
25

very very Apt!. Thanks a lot! – Ayo K Jun 07 '18 at 16:06

score 0 · Answer 2 · answered Jun 07 '18 at 15:37

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

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

Update a column in a table by joining multiple tables

2 Answers2