SQL 26 🛢️ Updating Data with UPDATE and WHERE
The UPDATE statement modifies existing rows in a table. It is one of the three data manipulation operations in SQL — alongside INSERT and DELETE — and it carries the most risk because it changes data that already exists, often data that other processes or users depend on. A misplaced UPDATE without a WHERE clause can overwrite every row in a table, and there is no built-in undo. Understanding how UPDATE works, how transactions protect against mistakes, and how the WHERE clause restricts which rows are affected is essential before running the statement against production data.
This chapter covers the syntax of UPDATE, the role of the WHERE clause in limiting scope, updating multiple columns and computed values, using subqueries and JOINs in updates, handling NULL values, transaction safety, and the patterns that prevent accidental mass updates.
Key point: UPDATE modifies existing rows. The WHERE clause determines which rows are affected. Without WHERE, every row in the table is updated. Always run a SELECT with the same WHERE clause first to verify the scope before executing the UPDATE.
Why UPDATE and WHERE exist
The data evolution problem. Data changes. A customer updates their address, a product price increases, an employee’s role changes, a bug in earlier data needs correction. The UPDATE statement is the mechanism for modifying existing rows without deleting and reinserting them, preserving relationships, keys, and history where applicable.
The scope problem. An UPDATE without a WHERE clause affects every row in the table. This is occasionally intentional — setting a default value for all rows — but it is far more often a mistake. The WHERE clause is the mechanism that limits an UPDATE to the rows that should change, and its correct use is the difference between a successful modification and a catastrophic one.
The consistency problem. Updates may need to maintain relationships between tables. When a customer’s primary key changes, foreign keys in other tables must be updated. When an order’s status changes, related inventory must be adjusted. Transactions ensure that a set of related updates either all succeed or all fail, preserving consistency.
The concurrency problem. In a multi-user database, two users may attempt to update the same row simultaneously. Without proper transaction isolation, one update may overwrite the other, or both may read stale data and write conflicting values. Understanding isolation levels and locking behavior is part of writing correct updates.
The audit problem. Changes to data may need to be traced. Who changed what, when, and why? Some systems maintain audit tables or history tables populated by triggers. Others rely on application-level logging. The UPDATE statement itself is transient; the record of what it changed must be captured separately.
a. Basic UPDATE syntax
The UPDATE statement has three parts: the table to modify, the column-value assignments, and the WHERE clause that identifies which rows to update.
UPDATE table_name
SET column1 = value1,
column2 = value2
WHERE condition;A single-column update:
UPDATE employees
SET salary = 65000
WHERE employee_id = 101;A multi-column update:
UPDATE employees
SET salary = 65000,
job_title = 'Senior Developer',
updated_at = CURRENT_TIMESTAMP
WHERE employee_id = 101;The SET clause lists the assignments. Each assignment is column = expression, and multiple assignments are separated by commas. The expressions can be literal values, other columns, functions, or subqueries.
The WHERE clause is syntactically optional but operationally mandatory for any targeted update. Omitting it updates every row:
-- Dangerous: updates every employee's salary
UPDATE employees
SET salary = 65000;This is valid SQL and will execute. It is almost never what the author intended.
b. The WHERE clause: scope and safety
The WHERE clause accepts the same conditions as a SELECT query: comparison operators, BETWEEN, IN, LIKE, IS NULL, and logical operators AND, OR, and NOT. The same condition in a SELECT identifies the rows that will be affected by an UPDATE.
UPDATE products
SET discount = 0.10
WHERE category = 'Electronics'
AND price > 500;The recommended practice is to run the SELECT first:
SELECT product_id, name, price
FROM products
WHERE category = 'Electronics'
AND price > 500;This verifies the scope before executing the UPDATE. If the SELECT returns the expected rows, the UPDATE will affect the same rows.
Conditions involving NULL require IS NULL or IS NOT NULL, not = NULL. A comparison with NULL yields unknown, which is treated as false in a WHERE clause, so WHERE column = NULL never matches any rows.
-- Correct
UPDATE customers
SET status = 'inactive'
WHERE last_login IS NULL;
-- Incorrect: never matches
UPDATE customers
SET status = 'inactive'
WHERE last_login = NULL;c. Computed values and expressions
The SET clause can reference the column being updated, compute new values from existing values, and use functions.
-- Increase salary by 10%
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';
-- Concatenate strings
UPDATE users
SET full_name = first_name || ' ' || last_name
WHERE full_name IS NULL;
-- Use a function
UPDATE orders
SET shipped_at = CURRENT_TIMESTAMP
WHERE status = 'ready_to_ship';The right-hand side of each assignment is evaluated using the row’s values before any assignments in the same statement are applied. In standard SQL, SET a = b, b = a swaps the values. Some database systems, notably MySQL, evaluate assignments left to right and would set a to the original b, then b to the new a. Consult the specific database’s documentation when the order of assignments matters.
d. Updating with subqueries and joins
An UPDATE can set values based on data in another table. A subquery in the WHERE clause filters rows based on related data:
UPDATE orders
SET priority = 'high'
WHERE customer_id IN (
SELECT customer_id
FROM customers
WHERE tier = 'premium'
);A subquery in the SET clause can pull a value from another table:
UPDATE products
SET price = (
SELECT AVG(price)
FROM products
WHERE category = 'Accessories'
)
WHERE category = 'Accessories'
AND price IS NULL;Some databases support UPDATE with a FROM clause (PostgreSQL, SQL Server) or a JOIN (MySQL):
-- PostgreSQL
UPDATE orders o
SET customer_name = c.name
FROM customers c
WHERE o.customer_id = c.id;
-- MySQL
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.customer_name = c.name;These forms are efficient but database-specific. The subquery form works across all major databases.
e. Transactions and rollback
Updates are not permanent until the transaction commits. Wrapping an UPDATE in a transaction allows it to be rolled back if the result is not what was intended.
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;
-- Check the result
SELECT * FROM accounts WHERE account_id IN (1, 2);
-- If correct:
COMMIT;
-- If not:
-- ROLLBACK;The pattern of BEGIN, run the update, verify with a SELECT, then COMMIT or ROLLBACK is the standard safety mechanism. It is especially important for updates that affect many rows or that touch financial or otherwise critical data.
Isolation levels determine what other transactions see while a transaction is in progress. At the default isolation level in most databases (READ COMMITTED), other transactions see the old values until the transaction commits. Higher isolation levels (REPEATABLE READ, SERIALIZABLE) provide stronger guarantees at the cost of concurrency. The isolation level is set per transaction or per session and should match the consistency requirements of the application.
f. Special considerations
Updating primary keys. Primary key updates are allowed but should be rare. If foreign keys reference the primary key, they must be updated consistently or defined with ON UPDATE CASCADE. Changing a primary key is often a sign that the key was not stable, and stable keys are preferred for precisely this reason.
Updating with LIMIT. MySQL supports UPDATE ... LIMIT n, which restricts the update to the first n matching rows. This is useful for batch processing but is not standard SQL. PostgreSQL does not support LIMIT in UPDATE directly; the equivalent uses a subquery with LIMIT.
Updating with ORDER BY. MySQL supports UPDATE ... ORDER BY, which determines the order in which rows are updated. This matters when the update involves unique constraints and the order of updates affects whether a constraint is violated.
The RETURNING clause. PostgreSQL and some other databases support RETURNING, which returns the updated rows as if they had been selected:
UPDATE products
SET price = price * 0.9
WHERE category = 'Clearance'
RETURNING product_id, name, price;This is useful for confirming what changed without a separate SELECT.
Complete Example Session
-- ============================================
-- PART 1: CREATE A SAMPLE TABLE
-- ============================================
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary NUMERIC(10, 2),
hired_date DATE,
active BOOLEAN
);
INSERT INTO employees VALUES
(101, 'Alice', 'Nguyen', 'Engineering', 72000, '2020-03-15', TRUE),
(102, 'Bob', 'Martinez','Engineering', 68000, '2021-06-01', TRUE),
(103, 'Carol', 'Okafor', 'Sales', 55000, '2019-11-20', TRUE),
(104, 'David', 'Kim', 'Sales', 62000, '2022-01-10', TRUE),
(105, 'Eve', 'Petrov', 'Marketing', 58000, '2020-08-05', FALSE);-- ============================================
-- PART 2: SINGLE ROW UPDATE
-- ============================================
-- Update one employee by primary key.
UPDATE employees
SET salary = 75000
WHERE employee_id = 101;-- ============================================
-- PART 3: VERIFY THE UPDATE
-- ============================================
SELECT employee_id, first_name, salary
FROM employees
WHERE employee_id = 101;-- ============================================
-- PART 4: MULTI-COLUMN UPDATE
-- ============================================
-- Change several columns at once.
UPDATE employees
SET salary = 70000,
department = 'Product',
active = TRUE
WHERE employee_id = 105;-- ============================================
-- PART 5: CONDITIONAL UPDATE WITH AND
-- ============================================
-- Apply a raise to a filtered set.
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'Engineering'
AND active = TRUE;-- ============================================
-- PART 6: UPDATE WITH IN
-- ============================================
-- Update rows matching a list of values.
UPDATE employees
SET active = FALSE
WHERE employee_id IN (103, 104);-- ============================================
-- PART 7: UPDATE WITH NULL CHECK
-- ============================================
-- Use IS NULL, not = NULL.
UPDATE employees
SET salary = 50000
WHERE salary IS NULL;-- ============================================
-- PART 8: UPDATE WITH SUBQUERY
-- ============================================
-- Set values based on another table.
UPDATE employees
SET salary = (
SELECT AVG(salary) FROM employees
)
WHERE department = 'Marketing';-- ============================================
-- PART 9: TRANSACTION WITH ROLLBACK
-- ============================================
-- Verify before committing.
BEGIN;
UPDATE employees
SET salary = salary * 1.20
WHERE department = 'Sales';
-- Inspect the result
SELECT employee_id, first_name, salary
FROM employees
WHERE department = 'Sales';
-- If correct:
COMMIT;
-- If not:
-- ROLLBACK;-- ============================================
-- PART 10: RETURNING CLAUSE
-- ============================================
-- Return updated rows (PostgreSQL).
UPDATE employees
SET salary = salary * 1.03
WHERE department = 'Engineering'
RETURNING employee_id, first_name, salary;These ten parts cover the complete UPDATE workflow: creating a table, updating single rows, verifying, multi-column updates, conditional filtering, NULL handling, subqueries, transaction safety, and the RETURNING clause. The transaction example shows the pattern of verify-before-commit that prevents accidental data loss.
Quick Reference
UPDATE Syntax
| Clause | Purpose |
|---|---|
UPDATE table | Specifies the table to modify |
SET col = value | Assigns a new value |
WHERE condition | Limits which rows are affected |
RETURNING cols | Returns updated rows (PostgreSQL, SQLite) |
WHERE Operators
| Operator | Example |
|---|---|
= | WHERE id = 5 |
<> or != | WHERE status <> 'active' |
BETWEEN | WHERE price BETWEEN 10 AND 50 |
IN | WHERE category IN ('A', 'B') |
LIKE | WHERE name LIKE 'A%' |
IS NULL | WHERE deleted_at IS NULL |
AND / OR / NOT | Combine conditions |
Transaction Commands
| Command | Purpose |
|---|---|
BEGIN | Start a transaction |
COMMIT | Make changes permanent |
ROLLBACK | Undo changes in the transaction |
SAVEPOINT name | Set a rollback point within a transaction |
Safety Patterns
| Pattern | Purpose |
|---|---|
| SELECT before UPDATE | Verify scope with same WHERE |
| BEGIN before UPDATE | Allow rollback if wrong |
| LIMIT (MySQL) | Restrict to a batch |
| RETURNING | Confirm what changed |
| Backup before mass update | Recovery if needed |
Best Practices
✅ Do This:
SELECT * FROM employees WHERE department = 'Sales'; -- Verify first
UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales';
BEGIN; -- Wrap in transaction
UPDATE ... ; COMMIT; -- Verify and commit
UPDATE ... WHERE deleted_at IS NULL; -- Correct NULL check
UPDATE ... RETURNING id, name; -- Confirm changes❌ Don’t Do This:
UPDATE employees SET salary = 50000; -- ❌ No WHERE
UPDATE employees SET status = 'x' WHERE col = NULL; -- ❌ Never matches
UPDATE employees SET a = 1; -- ❌ No transaction for mass update
UPDATE employees SET salary = 0 WHERE 1 = 1; -- ❌ Obfuscated no-WHERECommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| All rows updated | WHERE clause omitted | Always run SELECT with same WHERE first |
| No rows updated | NULL comparison with = | Use IS NULL |
| Wrong rows updated | WHERE condition too broad | Test with SELECT; narrow conditions |
| Update lost by concurrent transaction | No transaction or wrong isolation | Use transactions and appropriate isolation |
| Constraint violation | Update breaks foreign key or unique constraint | Update related tables consistently |
| Data corrupted | No backup before mass update | Take backup or use transaction |
| Trigger side effects | Triggers fire on UPDATE | Review triggers before mass updates |
Real-World Examples
1. Update Single Row by ID
UPDATE users SET email = 'new@example.com' WHERE user_id = 42;2. Apply a Percentage Raise
UPDATE salaries SET amount = amount * 1.03 WHERE department = 'Engineering';3. Set a Default for NULLs
UPDATE customers SET country = 'Unknown' WHERE country IS NULL;4. Update Based on Subquery
UPDATE orders
SET status = 'expired'
WHERE order_date < (
SELECT MIN(order_date) FROM orders WHERE status = 'active'
);5. Update with CASE
UPDATE products
SET tier = CASE
WHEN price < 10 THEN 'budget'
WHEN price < 100 THEN 'standard'
ELSE 'premium'
END;6. Update with Join (PostgreSQL)
UPDATE orders o
SET customer_name = c.name
FROM customers c
WHERE o.customer_id = c.id;7. Update with Join (MySQL)
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.customer_name = c.name;8. Update with Returning
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 101 AND quantity > 0
RETURNING product_id, quantity;9. Transaction with Savepoint
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If second update fails:
ROLLBACK TO after_debit;
COMMIT;10. Batch Update with Limit (MySQL)
UPDATE logs
SET archived = TRUE
WHERE archived = FALSE
ORDER BY created_at
LIMIT 1000;Visual
UPDATE Statement Anatomy
┌──────────────────────────────────────────────────────────────┐
│ UPDATE STATEMENT STRUCTURE │
│ │
│ UPDATE employees │
│ │ │ │
│ │ └── Table to modify │
│ │ │
│ SET salary = salary * 1.05, │
│ │ │ │ │
│ │ │ └── Expression using current value │
│ │ └── Column to modify │
│ │ │
│ WHERE department = 'Engineering' │
│ │ │ │
│ │ └── Which rows to update │
│ │ │
│ └── Filter condition │
│ │
│ Without WHERE: ALL rows are updated. │
│ With WHERE: only matching rows are updated. │
└──────────────────────────────────────────────────────────────┘Scope of UPDATE with and without WHERE
┌──────────────────────────────────────────────────────────────┐
│ WITH WHERE WITHOUT WHERE │
│ │
│ UPDATE employees UPDATE employees │
│ SET salary = 75000 SET salary = 75000 │
│ WHERE employee_id = 101; │
│ │
│ ┌────┬──────┬──────┐ ┌────┬──────┬──────┐ │
│ │ id │ name │ sal │ │ id │ name │ sal │ │
│ ├────┼──────┼──────┤ ├────┼──────┼──────┤ │
│ │101 │Alice │75000 │ ← changed│101 │Alice │75000 │ ← changed│
│ │102 │Bob │68000 │ │102 │Bob │75000 │ ← changed│
│ │103 │Carol │55000 │ │103 │Carol │75000 │ ← changed│
│ │104 │David │62000 │ │104 │David │75000 │ ← changed│
│ └────┴──────┴──────┘ └────┴──────┴──────┘ │
│ │
│ One row changed. Every row changed. │
└──────────────────────────────────────────────────────────────┘Transaction Safety Pattern
┌──────────────────────────────────────────────────────────────┐
│ VERIFY BEFORE COMMIT │
│ │
│ 1. BEGIN; │
│ │ │
│ ▼ │
│ 2. UPDATE employees SET salary = salary * 1.10 │
│ WHERE department = 'Engineering'; │
│ │ │
│ ▼ │
│ 3. SELECT * FROM employees WHERE department = 'Engineering';│
│ │ │
│ ├── Result correct? ──▶ 4a. COMMIT; │
│ │ │
│ └── Result wrong? ──▶ 4b. ROLLBACK; │
│ │
│ The transaction holds changes until COMMIT. │
│ ROLLBACK restores the original data. │
└──────────────────────────────────────────────────────────────┘UPDATE with Subquery vs JOIN
┌──────────────────────────────────────────────────────────────┐
│ TWO WAYS TO UPDATE FROM ANOTHER TABLE │
│ │
│ SUBQUERY (portable): │
│ UPDATE orders o │
│ SET customer_name = ( │
│ SELECT c.name FROM customers c │
│ WHERE c.id = o.customer_id │
│ ); │
│ │
│ JOIN (PostgreSQL): │
│ UPDATE orders o │
│ SET customer_name = c.name │
│ FROM customers c │
│ WHERE o.customer_id = c.id; │
│ │
│ JOIN (MySQL): │
│ UPDATE orders o │
│ JOIN customers c ON o.customer_id = c.id │
│ SET o.customer_name = c.name; │
│ │
│ Subquery works everywhere; JOIN is often faster. │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| UPDATE purpose | Modify existing rows in a table |
| SET clause | Lists column-value assignments |
| WHERE clause | Restricts which rows are affected |
| Without WHERE | Updates every row (dangerous) |
| NULL comparison | IS NULL, not = NULL |
| Computed value | SET salary = salary * 1.05 |
| Subquery | Set values from another table |
| Transaction | BEGIN, verify, COMMIT or ROLLBACK |
| RETURNING | Returns updated rows (PostgreSQL) |
| Database-specific | FROM/JOIN updates, LIMIT, ORDER BY |
Key takeaways:
- WHERE determines scope. Without it, every row is updated. Run a SELECT with the same WHERE clause first to verify which rows will be affected.
- NULL requires special handling.
WHERE column = NULLnever matches; useIS NULLorIS NOT NULL. - Computed values reference the current row.
SET salary = salary * 1.10increases each row’s salary by 10%, using its own value. - Subqueries and joins update from other tables. The subquery form is portable; database-specific JOIN syntax is often faster.
- Transactions provide safety. Wrap mass updates in
BEGIN, verify with SELECT, thenCOMMITorROLLBACKto prevent accidental data loss. - RETURNING confirms what changed. On databases that support it, returning the updated rows eliminates a separate SELECT.
- Primary key updates are rare and risky. If foreign keys reference the key, they must be updated consistently or defined with
ON UPDATE CASCADE. - Triggers fire on UPDATE. Mass updates may trigger side effects in other tables; review triggers before running them.
Remember: UPDATE is the statement that changes data that already exists, which makes it the most consequential of the data manipulation operations. The WHERE clause is the safety mechanism: it limits the update to the rows that should change. The habit of running a SELECT with the same WHERE clause before executing an UPDATE is the single most effective way to prevent mistakes. For updates that affect many rows or touch critical data, wrapping the operation in a transaction and verifying before committing provides a rollback path. The SET clause can reference existing values, compute new ones, and pull from other tables through subqueries or joins. Understanding these capabilities, and the database-specific variations, allows UPDATE to modify data precisely rather than broadly.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!