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

Recursive Queries in PostgreSQL

Introduction
Using CTEs with data-modifying statements
10. CTEs with UPDATE statement
Summary

Instruction

Excellent! Now we'll discuss how to use the UPDATE statement with CTEs. Look at this example:

WITH average_price AS (
  SELECT
    avg(price) AS avg_price
  FROM product
)

UPDATE product
SET price = price - (SELECT avg_price FROM average_price) / 100
WHERE grocery_id = 1;

The Green Shop Grocery, with id = 1, would like to discount its product prices by 1% of the average price for all products in all stores. In the WITH clause, we select average price for all products in all stores. This information is then used in our UPDATE statement. Last, the prices of all products in the grocery store with id = 1 are changed to the new, lower prices.

Exercise

Change the price of all products with a price between 0.50 and 1.00, raising them by $0.02. Use a CTE to select which products to modify.

Stuck? Here's a hint!

Use a WITH query to select the ids of all products priced between 0.50 and 1.00. Only change the product price if the product ID matches one of those returned by the CTE.