SQL UPDATE — Guide to Modifying Database Records

UPDATE modifies existing rows in a table. You specify which columns to change with SET and which rows to target with WHERE. Unlike INSERT (which adds rows) or DELETE (which removes them), UPDATE changes values in place — the row keeps its identity, its primary key, and its position in the table.

This guide covers single-row and multi-row updates, UPDATE with JOIN, conditional updates using CASE, and the safety patterns that prevent accidental mass modification.

UPDATE Syntax

The basic UPDATE statement has three parts — the table, the SET clause, and the WHERE clause:

UPDATE employees SET salary = 72000 WHERE employee_id = 1001;

You can update multiple columns in a single statement:

UPDATE products SET price = 29.99, stock_quantity = 150, last_modified = CURRENT_TIMESTAMP WHERE product_code = 'ABC123';

Column values in the SET clause can reference other columns in the same row. This runs the calculation per row, using the row's current values:

UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

Without a WHERE clause, UPDATE modifies every row in the table. This is almost never intentional. Every database allows it because there are legitimate use cases (resetting a flag on all rows, for example), but in practice, a missing WHERE clause is the single most common cause of data corruption from UPDATE statements.

UPDATE with WHERE Patterns

The WHERE clause controls which rows are modified. Write it once, test it with SELECT, then reuse it in the UPDATE.

The pattern: write your WHERE clause, run SELECT * FROM table WHERE ... to see exactly which rows will change, verify the count and the values, then swap SELECT for UPDATE.

-- Step 1: verify which rows match SELECT employee_id, first_name, salary FROM employees WHERE department = 'Sales' AND hire_date < '2024-01-01'; -- Step 2: if the results look correct, update UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales' AND hire_date < '2024-01-01';

Common WHERE patterns with UPDATE:

-- Update by primary key (single row) UPDATE customers SET email = 'new@example.com' WHERE customer_id = 42; -- Update with IN (specific set of rows) UPDATE products SET discontinued = true WHERE product_id IN (101, 205, 318); -- Update with BETWEEN UPDATE orders SET priority = 'urgent' WHERE order_date BETWEEN '2025-12-20' AND '2025-12-31'; -- Update NULLs UPDATE employees SET phone = 'not provided' WHERE phone IS NULL;

UPDATE with JOIN

UPDATE with JOIN modifies rows in one table based on data from another. The syntax differs across databases.

All three do the same thing: increase salaries by 10% for employees in the Sales department. The structural difference is where the join condition lives.

If you need a portable alternative that works on any SQL database, use a correlated subquery:

UPDATE employees SET salary = salary * 1.1 WHERE department_id IN ( SELECT department_id FROM departments WHERE department_name = 'Sales' );

Conditional UPDATE with CASE

CASE in the SET clause lets you apply different values to different rows in a single pass. This replaces multiple UPDATE statements with one:

UPDATE employees SET bonus = CASE WHEN performance_rating >= 4.5 THEN salary * 0.15 WHEN performance_rating >= 3.5 THEN salary * 0.10 WHEN performance_rating >= 2.5 THEN salary * 0.05 ELSE 0 END WHERE review_year = 2025;

Other practical CASE update patterns:

-- Status transitions based on date UPDATE subscriptions SET status = CASE WHEN end_date < CURRENT_DATE THEN 'expired' WHEN end_date < CURRENT_DATE + INTERVAL '30 days' THEN 'expiring_soon' ELSE status END; -- Data normalization in one pass UPDATE addresses SET state = UPPER(TRIM(state)) WHERE LENGTH(state) = 2;

Transaction Safety

Wrap UPDATE statements in transactions whenever the consequences of a wrong change are expensive. The pattern is simple: BEGIN, UPDATE, verify, then COMMIT or ROLLBACK.

BEGIN; UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 'PROD001'; -- Check the result before committing SELECT product_id, quantity FROM inventory WHERE product_id = 'PROD001'; -- If the quantity looks correct: COMMIT; -- If something is wrong: -- ROLLBACK;

For audit trails, include a timestamp column that records when each row was last modified:

UPDATE user_sessions SET last_activity = NOW(), session_duration = EXTRACT(EPOCH FROM NOW() - login_time) WHERE session_id = 'abc123';

Before running UPDATE statements on production tables, knowing which downstream reports and dashboards depend on the data prevents unexpected side effects. Data lineage shows these dependencies. Dawiso's catalog traces how tables feed into reports, so teams can coordinate UPDATE operations with downstream consumers.

Batch Updates for Large Tables

Updating millions of rows in a single statement is dangerous. The database holds locks on all affected rows until the transaction commits. On busy tables, this means other queries queue up behind the lock, the transaction log grows to accommodate the rollback data, and replication followers fall behind.

The solution is batching. Process a fixed number of rows, commit, then repeat:

-- MySQL: batch with LIMIT UPDATE orders SET status = 'archived' WHERE order_date < '2023-01-01' AND status = 'completed' LIMIT 5000; -- Repeat until 0 rows affected -- SQL Server: batch with TOP UPDATE TOP(5000) orders SET status = 'archived' WHERE order_date < '2023-01-01' AND status = 'completed'; -- Repeat until @@ROWCOUNT = 0 -- PostgreSQL: batch with a CTE WITH batch AS ( SELECT order_id FROM orders WHERE order_date < '2023-01-01' AND status = 'completed' LIMIT 5000 ) UPDATE orders SET status = 'archived' WHERE order_id IN (SELECT order_id FROM batch);

Between batches, the database releases locks, the transaction log can be truncated, and replication catches up. For very large updates during business hours, add a short delay between batches to reduce load.

Database-Specific Features

PostgreSQL supports UPDATE ... RETURNING, which gives you back the modified rows without a separate SELECT:

UPDATE employees SET salary = salary * 1.05 WHERE department = 'Engineering' RETURNING employee_id, first_name, salary;

MySQL supports UPDATE ... ORDER BY ... LIMIT, useful for processing the oldest or newest rows first:

UPDATE support_tickets SET priority = 'high' WHERE status = 'open' AND created_at < '2025-01-01' ORDER BY created_at ASC LIMIT 100;

SQL Server provides the OUTPUT clause (similar to PostgreSQL's RETURNING) and supports UPDATE ... FROM ... JOIN syntax:

UPDATE employees SET salary = salary * 1.05 OUTPUT INSERTED.employee_id, INSERTED.salary WHERE department = 'Engineering';

Common Mistakes

Missing WHERE clause. The most destructive mistake. UPDATE employees SET salary = 0 without WHERE sets every salary to zero. Recovery requires a backup restore. Prevention: always use the SELECT-first pattern, and run production updates inside a transaction.

Data type mismatch in SET values. Setting a date column to a string that looks like a date works in MySQL but fails in PostgreSQL. Use explicit date literals (DATE '2025-01-15') or parameterized queries.

Deadlocks from concurrent updates. Two sessions updating overlapping sets of rows in different order can deadlock. The fix: always update rows in a consistent order (by primary key), and keep transactions short.

Updating a column used in the WHERE clause. If your WHERE clause filters on status = 'pending' and your SET clause changes status to 'processed', the row may or may not be re-evaluated depending on the engine. In practice, this works correctly (the WHERE is evaluated before the SET), but understanding this prevents confusion when debugging.