SQL UPDATE for Updating Data
Data in a database is rarely static. Customers change their email addresses, product prices get adjusted, order statuses move from "pending" to "shipped," and employee records need corrections. The SQL UPDATE statement is the command that makes all of these changes possible, allowing you to modify existing data in one or more rows of a table.
While the syntax looks simple on the surface, the UPDATE statement carries real risk. A missing WHERE clause can overwrite every row in a table in an instant, and a poorly written condition can corrupt data silently. This guide teaches you how to use UPDATE correctly and safely, covering single-column and multi-column updates, the critical importance of the WHERE clause, and how to use subqueries to build dynamic update logic.
The Sample Schema
All examples in this guide use the following tables and data:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
city VARCHAR(50),
status VARCHAR(20) DEFAULT 'active'
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10,2),
in_stock BOOLEAN DEFAULT TRUE
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
status VARCHAR(20),
total_amount DECIMAL(10,2)
);
-- customers
INSERT INTO customers VALUES (1, 'Alice', 'Martin', 'alice@example.com', 'New York', 'active');
INSERT INTO customers VALUES (2, 'Bob', 'Jones', 'bob@example.com', 'Chicago', 'active');
INSERT INTO customers VALUES (3, 'Carol', 'Smith', 'carol@example.com', 'Denver', 'active');
INSERT INTO customers VALUES (4, 'Dave', 'Wilson', 'dave@oldmail.com', 'Boston', 'inactive');
INSERT INTO customers VALUES (5, 'Eve', 'Turner', 'eve@example.com', 'Chicago', 'active');
-- products
INSERT INTO products VALUES (101, 'Wireless Mouse', 'Electronics', 25.99, TRUE);
INSERT INTO products VALUES (102, 'USB-C Cable', 'Electronics', 9.99, TRUE);
INSERT INTO products VALUES (103, 'Notebook A5', 'Stationery', 4.50, TRUE);
INSERT INTO products VALUES (104, 'Mechanical Keyboard', 'Electronics', 89.99, TRUE);
INSERT INTO products VALUES (105, 'Desk Lamp', 'Furniture', 34.50, FALSE);
INSERT INTO products VALUES (106, 'Pen Pack', 'Stationery', 2.99, TRUE);
-- orders
INSERT INTO orders VALUES (1001, 1, '2024-01-15', 'completed', 150.00);
INSERT INTO orders VALUES (1002, 2, '2024-03-22', 'pending', 299.99);
INSERT INTO orders VALUES (1003, 1, '2024-04-10', 'processing', 420.00);
INSERT INTO orders VALUES (1004, 3, '2024-05-05', 'pending', 85.50);
INSERT INTO orders VALUES (1005, 4, '2024-05-20', 'cancelled', 200.00);
INSERT INTO orders VALUES (1006, 5, '2024-06-01', 'pending', 310.00);
Basic Syntax: UPDATE ... SET ... WHERE
The UPDATE statement modifies existing rows in a table. Its basic structure has three parts:
UPDATE table_name
SET column_name = new_value
WHERE condition;
UPDATE table_namespecifies which table to modify.SET column_name = new_valuedefines which column to change and what its new value should be.WHERE conditionfilters which rows should be affected. Only rows matching the condition are updated.
Example: Updating a Single Row
Dave has a new email address. Let's update it:
UPDATE customers
SET email = 'dave@newmail.com'
WHERE customer_id = 4;
To verify:
SELECT customer_id, first_name, email FROM customers WHERE customer_id = 4;
| customer_id | first_name | |
|---|---|---|
| 4 | Dave | dave@newmail.com |
The email changed from dave@oldmail.com to dave@newmail.com. Only the row where customer_id = 4 was affected. Every other row in the table remains untouched.
Example: Updating Multiple Rows That Match a Condition
You can update many rows at once by using a WHERE clause that matches multiple rows. Let's mark all pending orders as "processing":
UPDATE orders
SET status = 'processing'
WHERE status = 'pending';
To verify:
SELECT order_id, status FROM orders ORDER BY order_id;
| order_id | status |
|---|---|
| 1001 | completed |
| 1002 | processing |
| 1003 | processing |
| 1004 | processing |
| 1005 | cancelled |
| 1006 | processing |
Three rows (1002, 1004, 1006) had a status of 'pending' and were all updated to 'processing' in a single statement. Orders 1001, 1003, and 1005 were unaffected because they did not match the WHERE condition.
Most databases report how many rows were affected after an UPDATE. Pay attention to this count. If you expected to update 3 rows but the database reports 0 or 5000, something is wrong with your WHERE clause.
UPDATE 3
-- PostgreSQL reports "UPDATE 3"
-- MySQL reports "Rows matched: 3 Changed: 3"
Using Expressions in SET
The new value does not have to be a literal. You can use expressions, functions, and even reference the column's current value:
-- Increase all Electronics prices by 10%
UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';
To verify:
SELECT product_id, product_name, price FROM products WHERE category = 'Electronics';
| product_id | product_name | price |
|---|---|---|
| 101 | Wireless Mouse | 28.59 |
| 102 | USB-C Cable | 10.99 |
| 104 | Mechanical Keyboard | 98.99 |
Each product's price was multiplied by 1.10, applying a 10% increase. The expression price * 1.10 references the current value of each row's price column and computes the new value from it.
More expression examples:
-- Apply a $5 discount
UPDATE products SET price = price - 5.00 WHERE product_id = 105;
-- Convert names to uppercase
UPDATE customers SET last_name = UPPER(last_name) WHERE customer_id = 1;
-- Set order date to today
UPDATE orders SET order_date = CURRENT_DATE WHERE order_id = 1003;
Updating Multiple Columns
You are not limited to changing one column per UPDATE statement. List multiple column = value pairs in the SET clause, separated by commas.
Syntax
UPDATE table_name
SET column1 = value1,
column2 = value2,
column3 = value3
WHERE condition;
Example: Updating a Customer's Email and City
Alice moved from New York to San Francisco and changed her email:
UPDATE customers
SET email = 'alice@newdomain.com',
city = 'San Francisco'
WHERE customer_id = 1;
To verify:
SELECT customer_id, first_name, email, city
FROM customers
WHERE customer_id = 1;
| customer_id | first_name | city | |
|---|---|---|---|
| 1 | Alice | alice@newdomain.com | San Francisco |
Both columns were updated in a single statement. This is both more efficient and more readable than running two separate UPDATE commands.
Example: Reactivating a Customer with Updated Information
UPDATE customers
SET status = 'active',
email = 'dave.wilson@newmail.com',
city = 'Seattle'
WHERE customer_id = 4;
To verify:
SELECT * FROM customers WHERE customer_id = 4;
| customer_id | first_name | last_name | city | status | |
|---|---|---|---|---|---|
| 4 | Dave | Wilson | dave.wilson@newmail.com | Seattle | active |
Three columns changed in one atomic operation: status, email, and city.
Example: Applying a Discount and Marking Out of Stock
UPDATE products
SET price = price * 0.80,
in_stock = FALSE
WHERE product_id = 104;
To verify:
SELECT product_id, product_name, price, in_stock
FROM products
WHERE product_id = 104;
| product_id | product_name | price | in_stock |
|---|---|---|---|
| 104 | Mechanical Keyboard | 79.19 | FALSE |
The price was reduced by 20% and the product was simultaneously marked as out of stock.
When updating multiple columns, all changes happen atomically within the same row. The database does not update price first and then in_stock as separate operations. Both columns are set to their new values at the same logical instant. Other queries reading the table will never see a partially updated row.
The Danger of UPDATE Without WHERE
This is the single most important safety lesson with the UPDATE statement. If you omit the WHERE clause, the UPDATE applies to every row in the table.
The Catastrophic Example
Imagine you meant to set Dave's status to inactive, but you forgot the WHERE clause:
-- ⚠️ DANGEROUS: No WHERE clause!
UPDATE customers
SET status = 'inactive';
SELECT customer_id, first_name, status FROM customers;
| customer_id | first_name | status |
|---|---|---|
| 1 | Alice | inactive |
| 2 | Bob | inactive |
| 3 | Carol | inactive |
| 4 | Dave | inactive |
| 5 | Eve | inactive |
Every single customer is now inactive. Not just Dave. The database faithfully did exactly what you asked: update the status column in every row, because no WHERE clause restricted which rows to change.
An UPDATE without WHERE modifies every row in the table. There is no confirmation prompt, no warning, and no undo button. The change is immediate and permanent (unless you are inside an uncommitted transaction).
This is one of the most common and devastating mistakes in SQL. It can corrupt critical business data in milliseconds.
How to Protect Yourself
1. Always write the WHERE clause first.
Before typing the SET values, write the WHERE condition and verify it with a SELECT:
-- Step 1: Verify which rows will be affected
SELECT customer_id, first_name, status
FROM customers
WHERE customer_id = 4;
-- Step 2: Only after confirming the correct rows, write the UPDATE
UPDATE customers
SET status = 'inactive'
WHERE customer_id = 4;
2. Use transactions for critical updates.
Wrap your UPDATE in a transaction so you can review the result before committing:
BEGIN;
UPDATE customers
SET status = 'inactive'
WHERE customer_id = 4;
-- Check the result
SELECT * FROM customers;
-- If everything looks correct:
COMMIT;
-- If something went wrong:
-- ROLLBACK;
3. Check the affected row count.
After running an UPDATE, look at the row count reported by your database. If you expected to update 1 row but the count says 5, immediately ROLLBACK.
4. Use a LIMIT clause as a safety net (where supported).
MySQL and PostgreSQL support LIMIT on UPDATE statements:
-- MySQL: update at most 1 row
UPDATE customers
SET status = 'inactive'
WHERE customer_id = 4
LIMIT 1;
Some database tools and ORMs have a safe mode that prevents UPDATE and DELETE statements without a WHERE clause from executing. In MySQL Workbench, this is enabled by default (SQL_SAFE_UPDATES). Consider enabling similar protections in your development environment.
-- MySQL: enable safe mode
SET SQL_SAFE_UPDATES = 1;
-- This will now be rejected:
UPDATE customers SET status = 'inactive';
-- ERROR: You are using safe update mode and you tried to update a table
-- without a WHERE that uses a KEY column.
The Same Risk Applies to Expressions
Forgetting WHERE is equally dangerous with computed values:
-- ⚠️ Intended: 10% raise for employee 42
-- Actual: 10% raise for EVERYONE
UPDATE employees
SET salary = salary * 1.10;
Every salary in the entire table just increased by 10%. With expressions, the damage is harder to reverse because you cannot simply set the column back to a static value; you would need to know the original values.
Using Subqueries in UPDATE
Subqueries bring dynamic logic to the UPDATE statement. Instead of hardcoding values, you can compute new values from other tables or from other rows in the same table.
Subquery in the SET Clause
You can use a scalar subquery (one that returns a single value) as the new value for a column.
Set each order's total_amount to the customer's average order amount:
UPDATE orders AS o
SET total_amount = (
SELECT AVG(o2.total_amount)
FROM orders AS o2
WHERE o2.customer_id = o.customer_id
)
WHERE status = 'processing';
Before the update, let's see what the averages look like:
- Customer 1 (Alice): AVG(150.00, 420.00) = 285.00
- Customer 2 (Bob): AVG(299.99) = 299.99
To verify:
SELECT order_id, customer_id, status, total_amount FROM orders ORDER BY order_id;
| order_id | customer_id | status | total_amount |
|---|---|---|---|
| 1001 | 1 | completed | 150.00 |
| 1002 | 2 | processing | 299.99 |
| 1003 | 1 | processing | 285.00 |
| 1004 | 3 | processing | 85.50 |
| 1005 | 4 | cancelled | 200.00 |
| 1006 | 5 | processing | 310.00 |
Order 1003 (Alice, processing) was updated to 285.00, which is Alice's average. The subquery is correlated: it references o.customer_id from the outer UPDATE, so it computes a different average for each row.
Subquery in the WHERE Clause
You can use subqueries to dynamically determine which rows to update.
Mark all products as out of stock if their price is above the average price:
UPDATE products
SET in_stock = FALSE
WHERE price > (
SELECT AVG(price)
FROM products
);
The average price is approximately 27.83. Products with a price above that threshold get marked as out of stock:
SELECT product_id, product_name, price, in_stock FROM products ORDER BY price DESC;
| product_id | product_name | price | in_stock |
|---|---|---|---|
| 104 | Mechanical Keyboard | 98.99 | FALSE |
| 105 | Desk Lamp | 34.50 | FALSE |
| 101 | Wireless Mouse | 28.59 | FALSE |
| 102 | USB-C Cable | 10.99 | TRUE |
| 103 | Notebook A5 | 4.50 | TRUE |
| 106 | Pen Pack | 2.99 | TRUE |
Deactivate customers who have not placed any orders:
UPDATE customers
SET status = 'inactive'
WHERE customer_id NOT IN (
SELECT DISTINCT customer_id
FROM orders
WHERE customer_id IS NOT NULL
);
To verify
SELECT customer_id, first_name, status FROM customers;
| customer_id | first_name | status |
|---|---|---|
| 1 | Alice | active |
| 2 | Bob | active |
| 3 | Carol | active |
| 4 | Dave | active |
| 5 | Eve | active |
Any customer whose ID does not appear in the orders table gets deactivated.
When using NOT IN with a subquery, ensure the subquery column does not contain NULL values, or the entire WHERE clause will fail silently (returning no rows). Use NOT EXISTS as a safer alternative:
UPDATE customers AS c
SET status = 'inactive'
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);
UPDATE with a JOIN (Database-Specific Syntax)
Some databases allow you to join tables directly in an UPDATE statement. This is powerful but the syntax varies significantly between databases.
MySQL syntax:
UPDATE products AS p
JOIN (
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
) AS cat_avg ON p.category = cat_avg.category
SET p.price = cat_avg.avg_price
WHERE p.in_stock = FALSE;
PostgreSQL syntax (using FROM):
UPDATE products AS p
SET price = cat_avg.avg_price
FROM (
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
) AS cat_avg
WHERE p.category = cat_avg.category
AND p.in_stock = FALSE;
SQL Server syntax:
UPDATE p
SET p.price = cat_avg.avg_price
FROM products AS p
JOIN (
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
) AS cat_avg ON p.category = cat_avg.category
WHERE p.in_stock = 0;
The join-based UPDATE syntax is not part of the SQL standard and differs across databases. If you need portability, use correlated subqueries in the SET clause instead, which work consistently across all major databases.
Example: Update Based on Data from Another Table
Suppose you want to set each order's total_amount to a value pulled from a different table:
-- A lookup table with adjusted amounts
CREATE TABLE order_adjustments (
order_id INT PRIMARY KEY,
adjusted_amount DECIMAL(10,2)
);
INSERT INTO order_adjustments VALUES (1002, 275.00);
INSERT INTO order_adjustments VALUES (1004, 90.00);
Update orders using the adjustment table:
UPDATE orders
SET total_amount = (
SELECT oa.adjusted_amount
FROM order_adjustments AS oa
WHERE oa.order_id = orders.order_id
)
WHERE order_id IN (
SELECT order_id
FROM order_adjustments
);
To verify:
SELECT order_id, total_amount FROM orders WHERE order_id IN (1002, 1004);
| order_id | total_amount |
|---|---|
| 1002 | 275.00 |
| 1004 | 90.00 |
The WHERE clause ensures only orders with a corresponding adjustment are updated. Without it, orders not found in order_adjustments would have their total_amount set to NULL (because the subquery would return no rows for them).
When using a subquery in the SET clause, always pair it with a matching WHERE clause that restricts the update to rows for which the subquery will return a result. Otherwise, rows without a match will be set to NULL.
Wrong (sets non-matching rows to NULL):
UPDATE orders
SET total_amount = (
SELECT oa.adjusted_amount
FROM order_adjustments AS oa
WHERE oa.order_id = orders.order_id
);
-- Orders without an adjustment now have total_amount = NULL!
Correct (only updates matching rows):
UPDATE orders
SET total_amount = (
SELECT oa.adjusted_amount
FROM order_adjustments AS oa
WHERE oa.order_id = orders.order_id
)
WHERE EXISTS (
SELECT 1
FROM order_adjustments AS oa
WHERE oa.order_id = orders.order_id
);
Common Mistakes
Mistake 1: Forgetting WHERE (The Most Dangerous Mistake)
Already covered in depth above, but worth repeating as the number one item in any list of UPDATE mistakes.
Wrong:
UPDATE products SET price = 0;
-- Every product is now free. Congratulations.
Correct:
UPDATE products SET price = 0 WHERE product_id = 106;
Mistake 2: Using the Wrong Comparison Operator
A subtle typo can change which rows are affected dramatically.
Wrong (updates all orders EXCEPT the one you wanted):
-- Intended: update order 1002
-- Actual: updates every order that is NOT 1002
UPDATE orders
SET status = 'shipped'
WHERE order_id != 1002;
Correct:
UPDATE orders
SET status = 'shipped'
WHERE order_id = 1002;
Mistake 3: Confusing SET with WHERE Conditions
The SET clause assigns new values. The WHERE clause filters rows. Mixing them up leads to logical errors.
Wrong (this sets the city to 'Chicago' AND filters on customer_id):
-- Developer wanted: find Chicago customers and update their status
-- What they wrote: update customer 2's city to 'Chicago'
UPDATE customers
SET city = 'Chicago'
WHERE customer_id = 2;
What they probably meant:
UPDATE customers
SET status = 'premium'
WHERE city = 'Chicago';
Mistake 4: Updating a Column Based on Its Own Stale Value
When updating multiple columns where one depends on the other, be aware that all SET expressions are evaluated using the row's values before the update.
UPDATE products
SET price = price * 2,
category = CASE WHEN price > 50 THEN 'Premium' ELSE 'Standard' END
WHERE product_id = 103;
If price is currently 4.50, you might expect: price becomes 9.00, and since 9.00 is not > 50, category becomes 'Standard'.
But will the CASE check use the old price (4.50) or the new price (9.00)? In standard SQL, all SET expressions use the original row values. So the CASE checks 4.50, not 9.00. The result is 'Standard' either way in this example, but the distinction matters when values are near the threshold.
In standard SQL, all expressions in the SET clause are evaluated against the pre-update values of the row. The order of columns in the SET clause does not matter. All assignments happen "simultaneously" from the database's perspective.
This means:
UPDATE t SET a = b, b = a WHERE id = 1;
This swaps the values of a and b without needing a temporary variable, because both assignments read the original values.
Verifying Updates Before Executing
A best practice workflow for any non-trivial UPDATE:
Step 1: Write a SELECT with the same WHERE clause
SELECT order_id, customer_id, status, total_amount
FROM orders
WHERE status = 'processing' AND customer_id = 1;
| order_id | customer_id | status | total_amount |
|---|---|---|---|
| 1003 | 1 | processing | 285.00 |
You see exactly 1 row. Good, that is what you expected.
Step 2: Run the UPDATE inside a transaction
BEGIN;
UPDATE orders
SET status = 'shipped',
total_amount = 290.00
WHERE status = 'processing' AND customer_id = 1;
-- Check: "UPDATE 1" means 1 row affected. Correct!
Step 3: Verify the result
SELECT order_id, customer_id, status, total_amount
FROM orders
WHERE order_id = 1003;
| order_id | customer_id | status | total_amount |
|---|---|---|---|
| 1003 | 1 | shipped | 290.00 |
Step 4: Commit or rollback
-- Everything looks correct
COMMIT;
-- Or if something is wrong:
-- ROLLBACK;
This SELECT first, then UPDATE inside a transaction pattern should become muscle memory. It takes a few extra seconds but can save you from hours of disaster recovery.
Returning Updated Rows
Some databases let you see the updated rows as part of the UPDATE statement itself, eliminating the need for a separate verification SELECT.
PostgreSQL:
UPDATE orders
SET status = 'shipped'
WHERE order_id = 1003
RETURNING order_id, customer_id, status, total_amount;
| order_id | customer_id | status | total_amount |
|---|---|---|---|
| 1003 | 1 | shipped | 290.00 |
SQL Server:
UPDATE orders
SET status = 'shipped'
OUTPUT INSERTED.order_id, INSERTED.status
WHERE order_id = 1003;
MySQL: Does not support RETURNING. Use a subsequent SELECT or ROW_COUNT().
Performance Considerations
-
Index your WHERE columns. An
UPDATEmust first find the rows to update. If theWHEREclause references unindexed columns on a large table, the database performs a full table scan. -
Minimize updated columns. Only include columns in the
SETclause that actually need to change. Some databases skip the physical write if the new value equals the old value, but not all do. -
Batch large updates. Updating millions of rows in a single statement can lock the table for an extended period. Break it into smaller batches:
-- Update 1000 rows at a time (MySQL)
UPDATE orders
SET status = 'archived'
WHERE status = 'completed'
AND order_date < '2023-01-01'
LIMIT 1000;
-- Repeat until 0 rows affected -
Watch for trigger overhead. If the table has
UPDATEtriggers, each affected row fires the trigger. On bulk updates, this can be surprisingly slow. -
Use EXPLAIN when using subqueries. Correlated subqueries in
SETorWHEREcan be expensive. Verify the execution plan to ensure indexes are being used.EXPLAIN
UPDATE products
SET in_stock = FALSE
WHERE price > (SELECT AVG(price) FROM products);
Quick Reference
| Pattern | Syntax |
|---|---|
| Single column, single row | UPDATE t SET col = val WHERE id = 1 |
| Multiple columns | UPDATE t SET col1 = val1, col2 = val2 WHERE id = 1 |
| Expression-based | UPDATE t SET price = price * 1.10 WHERE category = 'X' |
| Subquery in SET | UPDATE t SET col = (SELECT ...) WHERE EXISTS (SELECT ...) |
| Subquery in WHERE | UPDATE t SET col = val WHERE col2 > (SELECT AVG(col2) FROM t) |
| With transaction | BEGIN; UPDATE ...; SELECT ...; COMMIT; |
Summary
The SQL UPDATE statement modifies existing data in a table. Its power lies in its simplicity, but that same simplicity makes it dangerous when used carelessly.
Key takeaways:
- The basic structure is
UPDATE ... SET ... WHERE. TheSETclause defines new values, and theWHEREclause determines which rows to change. - You can update multiple columns in a single statement by separating
column = valuepairs with commas. All assignments use the pre-update values of the row. - Never run an
UPDATEwithout aWHEREclause unless you genuinely intend to modify every row in the table. This is the most common and most devastating SQL mistake. - Always verify first with a
SELECTusing the sameWHEREcondition. Use transactions so you canROLLBACKif the result is not what you expected. - Subqueries can be used in both the
SETclause (to compute dynamic values) and theWHEREclause (to dynamically determine which rows to update). When using a subquery inSET, always include a matchingWHEREorEXISTSclause to prevent unmatched rows from being set toNULL. - Different databases offer additional syntax for join-based updates (
FROMin PostgreSQL,JOINin MySQL and SQL Server), but correlated subqueries provide a portable alternative. - For performance, index your
WHEREcolumns, batch large updates, and useEXPLAINto verify execution plans.
Treat every UPDATE statement with respect. A well-written update keeps your data accurate. A careless one can undo months of work in a single keystroke.