Updating the column that the table was joined on-CodePudding

I need to update a column that two tables were joined on and I'm having a difficult time wrapping my head around it. This is for SQL Server. Loose example below...

User

ID | Name | GroupID |
---------------------
1  | Bob  |   100   |
2  | Alex |   300   |
3  | Sara |   300   |

Group

ID   |   Name  | 
----------------
100  | Produce |
200  | Cashier |
300  | Stocker |

GroupID is a foreign key to the Group table and they are being joined on that. I HAVE to update the GroupID column in User based on the Name column in Group. For example, I want Alex and Sara to change from 'Stocker' to 'Cashier'. My solution is below, but it doesn't seem to work.

UPDATE User
SET User.GroupID = G.ID
FROM User U
    JOIN Group G ON U.GroupID = G.ID
WHERE User = 'Sara' OR User = 'Alex'

Expected Result

User

ID | Name | GroupID |
---------------------
1  | Bob  |   100   |
2  | Alex |   200   |
3  | Sara |   200   |

CodePudding user response：

You don't need and updated with join .. but you could use a subquery for get the expected id from group

update user 
set User.GroupID = (select id 
from group where name = 'Cashier )
where User = 'Sara' OR User = 'Alex'