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
8. Delete rows by referring to another table
INSERT, UPDATE and DELETE with JOIN
INSERT, UPDATE, and DELETE with subqueries
Summary

Instruction

Fantastic! INSERT is not the only statement that allows you to refer to another table. The DELETE or UPDATE statements do as well.

Imagine that an employee forgot to enter prices for some products that just arrived. However, some of these products have already been included in orders, even though they have not been priced. We can't give things away, so we need to delete these products from those orders.

For such a problem, we use the DELETE FROM ... FROM structure. Here's a statement that will do the trick:

DELETE FROM Purchase 
FROM Product 
WHERE Product.Id = Purchase.ProductId 
AND Product.Price IS NULL;

After DELETE FROM, we have the table from which the rows will be deleted. Next is the second FROM clause and a second table, the one which we will use to determine which rows to delete. We can do this thanks to a WHERE that joins these tables with the condition Product.Id = Purchase.ProductId and specifies the condition Product.Price IS NULL. You could use other conditions here as well.

There is a shorter version of this syntax that omits the first FROM:

DELETE Purchase 
FROM Product 
WHERE Product.Id = Purchase.ProductId 
AND Product.Price IS NULL;

Exercise

A customer, Mr. Smith, recently placed an order. However, he has decided he does not need some of the products he purchased and would like to remove them from the order. Help the sales employee remove products with Id = 3 that were ordered by Mr. Smith from the Purchase table using

DELETE FROM ... FROM ...

Stuck? Here's a hint!

Use

WHERE Customer.Id = Purchase.CustomerId 
AND Customer.LastName = N'Smith' 
AND Purchase.ProductId = 3