SQL UPDATE Statement: Syntax, Usage, and Examples
The SQL UPDATE statement lets you modify existing data in a table. You can use it to change one field, multiple fields, or even apply updates conditionally. It’s one of the most powerful tools in SQL for maintaining accurate and current records.
How to Use the SQL UPDATE Statement
The basic syntax of an UPDATE statement in SQL looks like this:
sql
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;
UPDATE: The keyword that starts the update command in SQL.table_name: The table you want to update.SET: Specifies the column(s) and the new value(s).WHERE: Filters which rows to update.
Always use a WHERE clause unless you’re deliberately updating all rows. Forgetting it can overwrite every row in the table.
Here’s a simple example:
sql
UPDATE employees
SET salary = 60000
WHERE employee_id = 5;
This changes the salary of the employee with ID 5. Without the WHERE clause, everyone would get the new salary.
When to Use the SQL UPDATE Statement
You’ll use the SQL UPDATE statement whenever you need to change data without deleting or inserting new records. Here are some common scenarios:
Fixing Incorrect Data
Maybe someone typed in the wrong value or imported bad data. You can use the SQL UPDATE statement to fix those records:
sql
UPDATE products
SET price = 19.99
WHERE product_id = 102;
Bulk Modifying Rows
You might want to increase salaries, adjust inventory, or update a status across many rows:
sql
UPDATE orders
SET status = 'shipped'
WHERE order_date < '2024-01-01';
This kind of batch update is common in e-commerce and data cleanup tasks.
Updating With Joins
Need to pull in data from another table? You can perform an SQL update using select and join:
sql
UPDATE employees
SET department_name = d.name
FROM departments d
WHERE employees.department_id = d.id;
This is called an SQL UPDATE JOIN. It updates one table based on related data in another.
Examples of the SQL UPDATE Statement
Example 1: Update a Record in SQL
sql
UPDATE customers
SET phone = '123-456-7890'
WHERE customer_id = 1;
You’ve just corrected a customer’s phone number. Simple and direct.
Example 2: SQL Update and Logic
You can combine conditions using AND to fine-tune your updates:
sql
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 10 AND quantity > 0;
This ensures you don’t reduce the stock below zero.
Example 3: SQL UPDATE From SELECT
Sometimes the new value you need comes from another query. Here’s a classic example of using update in SQL with JOIN and SELECT:
sql
UPDATE employees
SET salary = (
SELECT AVG(salary)
FROM employees
WHERE department_id = 2
)
WHERE department_id = 1;
This updates everyone in department 1 to have the average salary of department 2.
Example 4: Updating Multiple Columns
sql
UPDATE users
SET last_login = NOW(), status = 'active'
WHERE user_id = 42;
You can update multiple fields at once. This helps reduce the number of queries and speeds up processing.
Learn More About the SQL UPDATE Statement
UPDATE Syntax SQL Best Practices
The general structure is always:
sql
UPDATE table
SET column = value
WHERE condition;
But keep these tips in mind:
- Always include a WHERE clause unless you’re updating everything.
- Use transactions if you’re updating large data sets. That way, you can roll back if something goes wrong.
- Preview your WHERE clause with a SELECT statement first.
sql
SELECT * FROM users WHERE status = 'inactive';
Then run:
sql
UPDATE users SET status = 'archived' WHERE status = 'inactive';
Update Query in SQL With Joins
Some databases like PostgreSQL and SQL Server support joins directly in UPDATE queries:
sql
UPDATE sales
SET region = r.name
FROM regions r
WHERE sales.region_id = r.id;
Others like MySQL use a slightly different syntax:
sql
UPDATE sales
JOIN regions ON sales.region_id = regions.id
SET sales.region = regions.name;
Both achieve the same goal: an SQL update using select logic across tables.
SQL Update Using Select in Subqueries
If joins feel too complex, you can use subqueries to pull in values:
sql
UPDATE employees
SET manager_id = (
SELECT id FROM managers WHERE name = 'Jordan'
)
WHERE department = 'Engineering';
You can think of this as a lookup table built into the query.
Using Update With SQL Functions
You can combine updates with built-in SQL functions to manipulate data:
sql
UPDATE customers
SET email = LOWER(email)
WHERE email IS NOT NULL;
Or:
sql
UPDATE orders
SET order_date = NOW()
WHERE order_date IS NULL;
This makes it easy to clean or normalize your data.
Update Command in SQL vs Insert
You might wonder when to update versus when to insert. If a record already exists and you want to modify it—use UPDATE. If it doesn’t exist yet—use INSERT. Some databases offer UPSERT or MERGE statements that can handle both at once.
sql
INSERT INTO users (id, name)
VALUES (1, 'Taylor')
ON CONFLICT (id)
DO UPDATE SET name = 'Taylor';
This is a hybrid of insert and update.
SQL UPDATE with Conditions on Joined Tables
You can even use conditions from both tables when performing an update join:
sql
UPDATE employees
SET bonus = 1000
FROM performance_reviews
WHERE employees.id = performance_reviews.employee_id
AND performance_reviews.rating = 'excellent';
That’s powerful—you’re using joined logic to selectively update rows.
Updating Based on Aggregated Data
Sometimes you want to update using GROUP BY or aggregate values. Here’s one way to do it:
sql
UPDATE products
SET stock = stock + temp.new_stock
FROM (
SELECT product_id, SUM(quantity) AS new_stock
FROM incoming_shipments
GROUP BY product_id
) AS temp
WHERE products.id = temp.product_id;
This approach uses a subquery to summarize, then joins it back for the update.
The SQL UPDATE statement gives you full control over existing data. Whether you’re fixing a typo, changing a status, syncing data from another table, or applying business rules, UPDATE gets the job done.