UPDATE pitfalls
- SQL course
- Beginner
- Lesson 81/115
- 4 min
Learn the danger of UPDATE without WHERE.
Update with subquery
You can use a subquery inside UPDATE to write a value from another table.
UPDATE products
SET price = (SELECT max_price FROM price_list WHERE pid = products.id);This query takes each product's price from the price_list table.
In products, price = (SELECT max_price is updated.
Update with subquery — details
The subquery must return a single value; otherwise it errors. A correlated subquery runs per row.
Practice Update with subquery
Set price to 10 for id=1 in products.
Starter code
-- update
UPDATE products
SET price = 10
WHERESample solution
UPDATE products
SET price = 10
WHERE id = 1Expected output
1 satır güncellendi
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Subquery
Question
UPDATE products
SET price = (____ max_price FROM price_list WHERE pid = products.id);
Answer
UPDATE products
SET price = (SELECT max_price FROM price_list WHERE pid = products.id);Explanation: The subquery in parentheses starts with SELECT and must return a single value.
2 UPDATE pitfalls: match the terms
| Term | Meaning |
|---|---|
UPDATE |
update |
SET |
new value |
SELECT |
subquery |
WHERE |
condition |
3 How many values should the subquery return in UPDATE?
- None
- Two values
- Unlimited
- A single value Correct answer
Explanation: The subquery must return a single value.