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

PostgreSQL INSERT, UPDATE, and DELETE Commands

Introduction
INSERT with SELECT
Referring to other tables
8. Delete rows by referring to another table
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 ... USING structure. Here's a statement that will do the trick:

DELETE FROM purchase
USING product
WHERE product.id = purchase.product_id
  AND product.price IS NULL;

After DELETE FROM, we name the table from which the rows will be deleted. Next is the USING clause and a second table, the one which we will use to determine which rows to delete. We can do this by adding a WHERE that joins these tables by the condition product.id = purchase.product_id and specifies the condition product.price IS NULL. You could use other conditions here as well.

Exercise

A customer, Mr. Smith, recently placed an order. However, he has decided he does not need one of the products and would like to remove it from the order. Help the sales employee remove the product from the purchase table that has a product ID of 3 and was ordered by Mr. Smith.

Use the following structure:

DELETE FROM ...
USING ...

Stuck? Here's a hint!

Use:

WHERE customer.id = purchase.customer_id
  AND customer.last_name = 'Smith'
  AND purchase.product_id = 3