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_store_id = 1;
In the WITH clause we select average price for all products in all stores. This information is then used in our UPDATE statement. 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. 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 price between 0.50 and 1.00, raising them by $0.02. Use CTE to select products to modify.
Stuck? Here's a hint!
Use the WITH query to select ids of all products having prices between 0.50 and 1.00. Update the product table, changing the product price, only if the id of the product belongs to the set of values obtained from the CTE.
