MS SQL Update table column from join with another

Setup

User table

Let's say you have a table #users of users...

	Name	Colour	Animal	GroupNumber
1	Alex	Red	Cat	0
2	Billy	Blue	Dog	0
3	Charlie	Green	Cat	0
4	Donald	Red	Cat	0
5	Elijah	Red	Cat	0
6	Fred	Green	Cat	0
7	Gary	Blue	Dog	0
8	Henry	Red	Cat	0
9	Ian	Blue	Dog	0
10	Jack	Blue	Dog	0
11	Kevin	Green	Cat	0
12	Liam	Blue	Dog	0

Where everyone is in group number 0.

Groups table

And you have a table #groups of combinations of these...

	Colour	Animal	Group
1	Red	Cat	1
2	Red	Dog	2
3	Green	Cat	3
4	Green	Dog	4
5	Blue	Cat	5
6	Blue	Dog	6

Updating

Option 1: Updating with WHERE

UPDATE #users SET [GroupNumber] = g.[Group] FROM #groups AS g WHERE g.[Animal] = #users.[Animal] AND g.[Colour] = #users.[Colour] This is the simplest type, without having to remember the difference between joins.
But it does require typing out the full table name for each WHERE clause.

Option 2: Updating with JOIN

UPDATE u SET u.[GroupNumber] = g.[Group] FROM #users AS u INNER JOIN #groups AS g ON g.[Animal] = u.[Animal] AND g.[Colour] = u.[Colour] This is a little more difficult to wrap your head around, but allows table aliases.

Result

Both of these produce the same result:

	Name	Colour	Animal	GroupNumber
1	Alex	Red	Cat	1
2	Billy	Blue	Dog	6
3	Charlie	Green	Cat	3
4	Donald	Red	Cat	1
5	Elijah	Red	Cat	1
6	Fred	Green	Cat	3
7	Gary	Blue	Dog	6
8	Henry	Red	Cat	1
9	Ian	Blue	Dog	6
10	Jack	Blue	Dog	6
11	Kevin	Green	Cat	3
12	Liam	Blue	Dog	6

Where everyone is in the correct group.

Comments

If the #groups table has multiple rows for the combinations, only the first row is used, no duplicates are added (thankfully).

Sources

• Stackoverflow - Join
• Stackoverflow - Where