UPDATE Operations
Overview
The UPDATE statement mutates existing data already resting on the hard drive. It is incredibly dangerous. If you execute an UPDATE without a strict WHERE clause, it will instantly overwrite that column for every single row in the entire table, destroying the database. Updates can use basic math (incrementing a value), string manipulation, or even data pulled from other tables via Joins.
Syntax
-- 1. Standard Update (MUST HAVE A WHERE CLAUSE!)
UPDATE users
SET status = 'premium', updated_at = CURRENT_TIMESTAMP
WHERE user_id = 45;
-- 2. Mathematical Update (Incrementing values inline)
UPDATE products
SET stock_count = stock_count - 1 -- Safely decrements stock!
WHERE product_id = 99;
-- 3. Updating based on a JOIN (PostgreSQL syntax)
-- Automatically grant a bonus to employees in the IT department
UPDATE employees e
SET salary = salary + 5000
FROM departments d
WHERE e.dept_id = d.id AND d.name = 'IT';Common Pitfalls
- The Missing WHERE Clause. Running
UPDATE users SET password = '123';will instantly change every single user's password in the system to '123', resulting in a catastrophic data breach and immediate termination of your job. ALWAYS write yourSELECTquery first to test theWHEREclause, then change it to anUPDATE. - Deadlocks. If Transaction A updates Row 1 then Row 2, and Transaction B updates Row 2 then Row 1, they crash into each other and the database freezes. Always update rows in a consistent, sorted order.
Interview Questions
UPDATE under the hood? Does it just rewrite the bytes?Usually, no. In engines like PostgreSQL (which uses MVCC - Multi-Version Concurrency Control), an UPDATE is actually a DELETE followed by an INSERT. It marks the old row as 'dead' and writes a brand new row with the updated data to the disk. (This requires regular 'Vacuuming' to clean up the dead rows).
Real-World Example
Applying a 10% global discount to all products currently in the 'Summer Clearance' category.
UPDATE products
SET
price = price * 0.90,
is_on_sale = TRUE
WHERE category_id = (SELECT id FROM categories WHERE name = 'Summer');Check Your Knowledge
Test your understanding of UPDATE Operations with these quick questions.