How to Update Data Using SQL
Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. Among the many operations SQL supports, updating existing data is one of the most critical and frequently performed tasks. Whether you’re correcting a typo, changing a customer’s contact details, or adjusting stock levels, the UPDATE statement is your go-to tool.
In this article, we’ll explore the syntax of SQL’s UPDATE statement, its common use cases, and best practices to avoid common pitfalls. We’ll also provide hands-on code examples to help you apply the concepts immediately.
Learn SQL online
Basic Syntax of the UPDATE Statement
The SQL UPDATE statement allows you to modify existing records in a table. Here’s the basic syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
table_name: The name of the table where the data will be updated.SET: Specifies the columns to update and their new values.WHERE: Filters the rows that should be updated.
Important: Always use a
WHEREclause unless you’re certain you want to update every row in the table.
Updating a Single Row
Suppose we have a customers table:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
We want to update the email address of a customer with customer_id = 101.
UPDATE customers
SET email = 'new_email@example.com'
WHERE customer_id = 101;
This will only affect the row where customer_id is 101.
Updating Multiple Columns
You can update more than one column in the same query:
UPDATE customers
SET name = 'Jane Doe',
email = 'jane.doe@example.com'
WHERE customer_id = 102;
Updating Multiple Rows
To update all customers with a specific domain in their email address:
UPDATE customers
SET email = REPLACE(email, '@old-domain.com', '@new-domain.com')
WHERE email LIKE '%@old-domain.com';
This uses SQL’s REPLACE() function to modify parts of the email string.
Conditional Update Using Subqueries
You can use subqueries in the SET clause or WHERE clause to derive values from other tables.
UPDATE products
SET price = price * 1.10
WHERE category_id IN (
SELECT category_id FROM categories WHERE category_name = 'Electronics'
);
This increases the price of all electronic products by 10%.
Update with JOIN
Some databases (like PostgreSQL, SQL Server, and MySQL 8+) support UPDATE with JOIN to update values based on another table.
UPDATE orders
JOIN customers ON orders.customer_id = customers.customer_id
SET orders.status = 'VIP'
WHERE customers.loyalty_points > 1000;
This updates the status of orders for VIP customers.
Best Practices
Always test with a
SELECTfirst: Before running anUPDATE, run aSELECTwith the sameWHEREclause to confirm the rows affected.Use transactions: Wrap updates in transactions when doing batch updates:
BEGIN TRANSACTION;
UPDATE ...;
COMMIT;
This provides rollback safety in case of an error.
Backup your data: Especially in production environments.
Avoid missing
WHEREclauses: AnUPDATEwithoutWHEREupdates all rows.Log changes: If auditing is required, consider inserting old values into a history table before updating.
Conclusion
The SQL UPDATE statement is a powerful tool for maintaining and correcting your data. With great power comes great responsibility—so always double-check your WHERE clause, test your queries, and consider using transactions and backups.
By mastering UPDATE, you not only maintain data integrity but also build confidence in performing safe and effective data operations.
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
