Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

How to Insert, Update, or Delete Data in MS SQL Server

Introduction
INSERT with SELECT
Referring to other tables
9. Update rows by referring to another table
INSERT, UPDATE and DELETE with JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

Very nice! You also can use references to other tables in an UPDATE statement. If you need to update a row using data from another table, you can write a statement like the one below:

UPDATE Purchase 
SET Purchase.TotalPrice = Product.Price * Purchase.Quantity 
FROM Product 
WHERE Purchase.ProductId = Product.Id;

This statement will update product amounts from the Purchase table using price details from the Product table. The additional table (Product) is given after FROM, which is listed after SET. The WHERE condition comes immediately after FROM; in this case, it is joining the two tables by ID (Purchase.ProductId = Product.Id). Notice that by choosing a new product price, we can change the amount in the order.

Exercise

The manager wants to give a raise to each employee who sold over $1000 in one order (based on the TotalPrice column in the Purchase table). Use UPDATE ... FROM to add $100 to the Salary of each Employee who meets the qualification.

Stuck? Here's a hint!

Use:

WHERE Purchase.EmployeeId = Employee.Id 
AND Purchase.TotalPrice > 1000;