SQL - UPDATE Statement
The UPDATE statement modifies existing records in a table. The fundamental syntax requires specifying the table name, columns to update with their new values, and a WHERE clause to identify which...
Key Insights
- UPDATE statements modify existing data in database tables and require a WHERE clause to avoid accidentally updating all rows
- Modern SQL supports updating multiple tables simultaneously through JOIN operations and using subqueries for complex conditional updates
- Transaction control and proper indexing on WHERE clause columns are critical for UPDATE performance and data integrity in production environments
Basic UPDATE Syntax
The UPDATE statement modifies existing records in a table. The fundamental syntax requires specifying the table name, columns to update with their new values, and a WHERE clause to identify which rows to modify.
UPDATE employees
SET salary = 75000
WHERE employee_id = 1001;
Without a WHERE clause, UPDATE affects every row in the table:
-- DANGEROUS: Updates all employees
UPDATE employees
SET department = 'Engineering';
Always verify your WHERE clause before executing. Use SELECT first to preview affected rows:
-- Preview the rows that will be updated
SELECT * FROM employees WHERE employee_id = 1001;
-- Then execute the update
UPDATE employees
SET salary = 75000
WHERE employee_id = 1001;
Updating Multiple Columns
Update multiple columns in a single statement by separating them with commas:
UPDATE employees
SET
salary = 80000,
job_title = 'Senior Developer',
last_modified = CURRENT_TIMESTAMP
WHERE employee_id = 1001;
This approach is more efficient than multiple UPDATE statements and maintains atomicity—all changes succeed or fail together.
Conditional Updates with CASE
Use CASE expressions for conditional logic within UPDATE statements:
UPDATE employees
SET salary = CASE
WHEN years_experience >= 10 THEN salary * 1.15
WHEN years_experience >= 5 THEN salary * 1.10
WHEN years_experience >= 2 THEN salary * 1.05
ELSE salary * 1.02
END,
bonus = CASE
WHEN performance_rating = 'Excellent' THEN salary * 0.20
WHEN performance_rating = 'Good' THEN salary * 0.10
ELSE 0
END
WHERE department = 'Engineering'
AND employment_status = 'Active';
This pattern allows complex business logic in a single database round-trip.
UPDATE with Subqueries
Subqueries enable updates based on data from other tables or aggregate calculations:
-- Update based on aggregate data
UPDATE products
SET average_rating = (
SELECT AVG(rating)
FROM reviews
WHERE reviews.product_id = products.product_id
)
WHERE EXISTS (
SELECT 1 FROM reviews
WHERE reviews.product_id = products.product_id
);
Using EXISTS in the WHERE clause ensures you only update products that have reviews:
-- Update employee salaries based on department average
UPDATE employees e
SET salary = (
SELECT AVG(salary) * 1.1
FROM employees
WHERE department = e.department
AND employee_id != e.employee_id
)
WHERE salary < (
SELECT AVG(salary)
FROM employees dept_avg
WHERE dept_avg.department = e.department
);
UPDATE with JOIN Operations
PostgreSQL and MySQL support different syntaxes for updating based on joined tables.
PostgreSQL syntax:
UPDATE orders
SET status = 'Shipped',
shipped_date = CURRENT_TIMESTAMP
FROM shipments
WHERE orders.order_id = shipments.order_id
AND shipments.tracking_number IS NOT NULL
AND orders.status = 'Processing';
MySQL syntax:
UPDATE orders o
INNER JOIN shipments s ON o.order_id = s.order_id
SET o.status = 'Shipped',
o.shipped_date = CURRENT_TIMESTAMP
WHERE s.tracking_number IS NOT NULL
AND o.status = 'Processing';
SQL Server supports both syntaxes but prefers the FROM clause:
UPDATE o
SET o.status = 'Shipped',
o.shipped_date = GETDATE()
FROM orders o
INNER JOIN shipments s ON o.order_id = s.order_id
WHERE s.tracking_number IS NOT NULL
AND o.status = 'Processing';
Updating with Calculated Values
Perform mathematical operations directly in UPDATE statements:
-- Apply percentage increase
UPDATE products
SET price = price * 1.08
WHERE category = 'Electronics'
AND last_price_update < DATE_SUB(CURRENT_DATE, INTERVAL 6 MONTH);
-- Increment counters
UPDATE articles
SET view_count = view_count + 1,
last_viewed = CURRENT_TIMESTAMP
WHERE article_id = 42;
Concatenate strings or modify text fields:
UPDATE customers
SET email = LOWER(email),
full_name = CONCAT(first_name, ' ', last_name)
WHERE email != LOWER(email)
OR full_name IS NULL;
Transaction Control and Rollback
Wrap UPDATE statements in transactions for safety in production:
BEGIN TRANSACTION;
UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1001;
UPDATE accounts
SET balance = balance + 500
WHERE account_id = 1002;
-- Verify the changes
SELECT account_id, balance
FROM accounts
WHERE account_id IN (1001, 1002);
-- If everything looks correct
COMMIT;
-- If something is wrong
-- ROLLBACK;
Use savepoints for complex multi-step updates:
BEGIN TRANSACTION;
UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 100;
SAVEPOINT after_inventory;
UPDATE orders SET status = 'Confirmed' WHERE order_id = 5000;
SAVEPOINT after_orders;
-- If order update fails, rollback to after inventory update
-- ROLLBACK TO SAVEPOINT after_inventory;
COMMIT;
Performance Considerations
Index columns used in WHERE clauses to prevent full table scans:
-- Create index on frequently updated columns
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_employees_dept ON employees(department, employment_status);
For large updates, batch the operations to avoid locking issues:
-- Instead of updating millions of rows at once
-- Update in batches
DECLARE @BatchSize INT = 10000;
WHILE EXISTS (
SELECT 1 FROM products
WHERE legacy_format = 1
AND updated = 0
)
BEGIN
UPDATE TOP (@BatchSize) products
SET data_format = 'new_format',
updated = 1
WHERE legacy_format = 1
AND updated = 0;
-- Small delay to reduce lock contention
WAITFOR DELAY '00:00:01';
END
Returning Updated Data
PostgreSQL and SQL Server support RETURNING/OUTPUT clauses to retrieve updated values:
PostgreSQL:
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales'
RETURNING employee_id, employee_name, salary;
SQL Server:
UPDATE employees
SET salary = salary * 1.10
OUTPUT
INSERTED.employee_id,
INSERTED.employee_name,
DELETED.salary AS old_salary,
INSERTED.salary AS new_salary
WHERE department = 'Sales';
This eliminates the need for separate SELECT statements to verify changes.
Common Pitfalls
Always specify WHERE clauses unless you genuinely need to update every row. Test with SELECT first:
-- Wrong: Updates all rows
UPDATE users SET active = 0;
-- Correct: Updates specific rows
UPDATE users SET active = 0 WHERE last_login < DATE_SUB(CURRENT_DATE, INTERVAL 2 YEAR);
Avoid updating primary keys or columns involved in foreign key relationships without CASCADE rules. Use transactions for multi-table updates to maintain referential integrity. Monitor long-running UPDATE operations that may cause blocking in high-concurrency environments.