Update with Join

Update with Join

Below are two similar and simple to understand examples of Update with Join.

Example 1 of Update with Join

We have two tables: Customers and Purchases:

Select * from Customers

Customer_ID	Customer_name	Order_Amount
3	AAA	Null
4	BBB	Null
5	CCC	Null
6	DDD	Null

Select * from Purchases

Purchase_ID	Customer_ID	Amount
106	6	300
203	3	500
505	5	750

Now we will update the Customers table based on Purchases table so that the Customers table should look like as below:

Customer_ID	Customer_name	Order_Amount
3	AAA	500
4	BBB	Null
5	CCC	750
6	DDD	300

Below update query will work for us:

UPDATE C
SET    C.Order_Amount = O.Amount
FROM   dbo.Customers C
INNER JOIN dbo.Purchases O ON C.Customer_ID = O.Customer_ID

Select * from Customers

Customer_ID	Customer_name	Order_Amount
3	AAA	500
4	BBB	Null
5	CCC	750
6	DDD	300

Example 2 of Update with Join

Below are two tables: Students and Groups

Select * from Students

Student_ID	Student_name	Student_Group	Student_Group_Name
1	AAA	G1	Null
2	BBB	G2	Null
3	CCC	G3	Null
4	DDD	G3	Null

Select * from Groups

Group_ID	Group_Name	Group_Members
G1	Green	300
G2	Red	500
G3	Yellow	750

We want to update Students table based on Groups table so that the Students table should look like as below:

Student_ID	Student_name	Student_Group	Student_Group_Name
1	AAA	G1	Green
2	BBB	G2	Red
3	CCC	G3	Yellow
4	DDD	G3	Yellow

Below update query will work for us:

UPDATE S
SET    S.Student_Group_Name = G.Group_Name
FROM   dbo.Students S
INNER JOIN dbo.Groups G ON S.Student_Group = G.Group_ID

Select * from Students

Student_ID	Student_name	Student_Group	Student_Group_Name
1	AAA	G1	Green
2	BBB	G2	Red
3	CCC	G3	Yellow
4	DDD	G3	Yellow