Subqueries in DML

UPDATE and DELETE accept subqueries in WHERE and SET. Here a clearance discount hits the titles nobody has ordered since June, then two statements try to delete below-average reviews:

UPDATE products SET price = ROUND(price * 0.9, 2)
WHERE id NOT IN (SELECT i.product_id FROM order_items AS i
                 JOIN orders AS o ON o.id = i.order_id
                 WHERE o.ordered_at >= '2026-06-01');
DELETE FROM reviews WHERE rating < (SELECT AVG(rating) FROM reviews);
DELETE r FROM reviews AS r
JOIN (SELECT AVG(rating) AS avg_rating FROM reviews) AS a ON r.rating < a.avg_rating;

Query OK, 2 rows affected (0.008 sec)
Rows matched: 2  Changed: 2  Warnings: 0
ERROR 1093 (HY000): You can't specify target table 'reviews' for update in FROM clause
Query OK, 2 rows affected (0.005 sec)

The NOT IN is safe because product_id is NOT NULL; Indexing Deep Dive and Designing Calm Interfaces drop to 26.10 and 30.60. Error 1093 forbids reading the target table in a subquery. Let the server compute the value first instead: join a materialized derived table (the average, 3.857), or nest one, rating < (SELECT a FROM (SELECT AVG(rating) AS a FROM reviews) AS t). Either removes reviews 3 and 7. Multi-table DELETE t FROM ... JOIN and UPDATE a JOIN b SET ... are MySQL 524 extensions. Test the WHERE as a SELECT first.