Topic 10 of 52
UPDATE SET
Overview
UPDATE modifies existing rows in a table. It is critical to use WHERE clauses correctly — a missing WHERE updates every single row in the table, potentially causing catastrophic data loss.
Syntax
sql
-- Basic UPDATE
UPDATE products SET price = 999.00 WHERE id = 42;
-- Update multiple columns
UPDATE users
SET
name = 'Priya Singh',
updated_at = NOW()
WHERE id = 101;
-- Update with calculation
UPDATE order_items SET
final_price = unit_price * (1 - discount_pct / 100.0)
WHERE order_id = 500;
-- Update based on another table (JOIN)
UPDATE products p
SET stock = p.stock - oi.quantity
FROM order_items oi
WHERE p.id = oi.product_id AND oi.order_id = 500;
-- Update with RETURNING
UPDATE users SET last_login = NOW() WHERE id = 42 RETURNING id, last_login;Common Pitfalls
- ALWAYS include a WHERE clause in UPDATE — forgetting it updates every row in the table!
- Test your WHERE condition with a SELECT first before running UPDATE to verify affected rows.
- Interview tip: Wrap critical UPDATEs in a transaction: BEGIN; UPDATE ...; SELECT to verify; COMMIT; — this lets you ROLLBACK if wrong.
Real-World Example
Applying a promotional discount to a product category:
example
sql
-- Apply 15% discount to all Electronics under ₹50,000
UPDATE products
SET
original_price = price,
price = ROUND(price * 0.85, 2),
sale_ends_at = NOW() + INTERVAL '7 days',
updated_at = NOW()
WHERE
category_id = (SELECT id FROM categories WHERE name = 'Electronics')
AND price < 50000
AND is_active = TRUE;
-- Safe pattern: preview before updating
-- Step 1: SELECT to verify what will be updated
SELECT id, name, price, ROUND(price * 0.85, 2) AS new_price
FROM products
WHERE category_id = 1 AND price < 50000 AND is_active = TRUE;
-- Step 2: Run UPDATE only after verifying the SELECT
-- Step 3: Verify the update
SELECT id, name, original_price, price FROM products
WHERE category_id = 1 AND sale_ends_at IS NOT NULL;