RSS Feed

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