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
INSERT, UPDATE and DELETE with JOIN
11. Update data using JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

Very well done!

As you probably guessed, JOIN is also used in UPDATE queries. Its usage is quite similar to how we used it in the INSERT INTO statement. Look at the query below, which decreases the price of a product if the order amount is more than 1000:

UPDATE Purchase 
SET Purchase.TotalPrice = Product.Price * 0.75 * Purchase.Quantity 
FROM Purchase 
JOIN Product ON Purchase.ProductId = Product.Id 
WHERE Purchase.TotalPrice > 1000;
We have to use the referenced table, Product, to get the price of the product; that's why we used JOIN in the SELECT clause.

Exercise

Decrease the value of ordered products (Purchase table) by 15% if the product's TotalPrice (Product table) exceeds 10.

Stuck? Here's a hint!

Use:

SET Purchase.TotalPrice = Product.Price * 0.85 * Purchase.Quantity