DbSchema Tutorial | SQL UPDATE STATEMENT
SQL, or Structured Query Language, is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS). One of the most commonly used SQL statements is the UPDATE statement, which allows you to modify existing records in a table.
In this tutorial, we will delve into the UPDATE statement in SQL and cover some important concepts related to the topic.
SQL UPDATE Statement
The SQL UPDATE statement is used to modify the existing records in a table. You can change single or multiple records using this statement.
Syntax
The basic syntax for the UPDATE statement is as follows:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
table_name: Name of the table where the records will be updated.column1, column2, ...: Columns that you want to modify.value1, value2, ...: New values that you want to assign to the specified columns.WHERE: Optional. Specifies which record(s) to update. If omitted, all records in the table will be updated.
Example
Let’s consider the following Students table:
| StudentID | Name | Age |
|---|---|---|
| 1 | John | 20 |
| 2 | Alice | 22 |
| 3 | Bob | 21 |
To update the age of Alice to 23, you would run the following query:
UPDATE Students
SET Age = 23
WHERE Name = 'Alice';
Result:
After running the query, the Students table will look like this:
| StudentID | Name | Age |
|---|---|---|
| 1 | John | 20 |
| 2 | Alice | 23 |
| 3 | Bob | 21 |
Explanation:
Here, the UPDATE statement modified the Age of Alice to 23 as specified in the SET clause. The WHERE clause is used to specify the record to be updated. Without the WHERE clause, all records in the table would have been updated.
SQL UPDATE Statement to Modify Single or Multiple Data Fields
As seen in the example above, the UPDATE statement can be used to modify a single data field. However, it can also be used to modify multiple data fields at once.
Example
Let’s say we want to update the Name of Alice to ‘Alicia’ and her Age to 24. The query would be:
UPDATE Students
SET Name = 'Alicia', Age = 24
WHERE StudentID = 2;
Result:
After running the query, the Students table will look like this:
| StudentID | Name | Age |
|---|---|---|
| 1 | John | 20 |
| 2 | Alicia | 24 |
| 3 | Bob | 21 |
Explanation:
In this case, both the Name and Age of the record with StudentID 2 were updated.
SQL UPDATE Statement Using Date-Time Functions
You can also use date-time functions in the SET clause of the UPDATE statement.
Example
Let’s consider the following Orders table:
| OrderID | ProductName | OrderDate |
|---|---|---|
| 1 | Product A | 2023-01-01 |
| 2 | Product B | 2023-02-01 |
| 3 | Product C | 2023-03-01 |
To update the OrderDate of the first order to the current date, you can use the CURRENT_DATE function:
UPDATE Orders
SET OrderDate = CURRENT_DATE
WHERE OrderID = 1;
Result:
After running the query, the Orders table will look like this:
| OrderID | ProductName | OrderDate |
|---|---|---|
| 1 | Product A | 2023-08-23 |
| 2 | Product B | 2023-02-01 |
| 3 | Product C | 2023-03-01 |
Explanation:
Here, the OrderDate of the first order was updated to the current date.
SQL UPDATE Syntax with Subqueries and JOIN Statement
You can also use subqueries and JOIN statements in the UPDATE statement.
Example
Let’s consider the following two tables, Orders and OrderDetails:
Orders table:
| OrderID | CustomerID |
|---|---|
| 1 | C1 |
| 2 | C2 |
| 3 | C3 |
OrderDetails table:
| OrderID | ProductID | Quantity |
|---|---|---|
| 1 | P1 | 10 |
| 2 | P2 | 20 |
| 3 | P3 | 30 |
To update the Quantity in the OrderDetails table based on a condition in the Orders table, you can use a subquery or JOIN statement. Here, we will use a subquery:
UPDATE OrderDetails
SET Quantity = 50
WHERE OrderID IN (
SELECT OrderID
FROM Orders
WHERE CustomerID = 'C1'
);
Result:
After running the query, the OrderDetails table will look like this:
| OrderID | ProductID | Quantity |
|---|---|---|
| 1 | P1 | 50 |
| 2 | P2 | 20 |
| 3 | P3 | 30 |
Explanation:
Here, the Quantity of the first order was updated to 50 because the CustomerID of the first order in the Orders table is ‘C1’.
Updating a Table with Data from Another Table
You can update a table using data from another table.
Example
Let’s consider the following two tables, Customers and Orders:
Customers table:
| CustomerID | CustomerName |
|---|---|
| C1 | John |
| C2 | Alice |
| C3 | Bob |
Orders table:
| OrderID | CustomerID | CustomerName |
|---|---|---|
| 1 | C1 | |
| 2 | C2 | |
| 3 | C3 |
To update the CustomerName in the Orders table using the CustomerName from the Customers table, you can use a subquery or JOIN statement. Here, we will use a JOIN statement:
UPDATE Orders
SET Orders.CustomerName = Customers.CustomerName
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
Result:
After running the query, the Orders table will look like this:
| OrderID | CustomerID | CustomerName |
|---|---|---|
| 1 | C1 | John |
| 2 | C2 | Alice |
| 3 | C3 | Bob |
Explanation:
Here, the CustomerName in the Orders table was updated using the CustomerName from the Customers table.
SQL Update Multiple Rows
The UPDATE statement can be used to update multiple rows at once.
Example
Let’s consider the following Products table:
| ProductID | Price |
|---|---|
| P1 | 10 |
| P2 | 20 |
| P3 | 30 |
To increase the price of all products by 10%, you would run the following query:
UPDATE Products
SET Price = Price * 1.10;
Result:
After running the query, the Products table will look like this:
| ProductID | Price |
|---|---|
| P1 | 11 |
| P2 | 22 |
| P3 | 33 |
Explanation:
Here, the Price of all products was increased by 10%.
Update Data by Omitting WHERE Clause
If you omit the WHERE clause in the UPDATE statement, all records in the table will be updated.
Example
Let’s consider the following Products table:
| ProductID | Price |
|---|---|
| P1 | 10 |
| P2 | 20 |
| P3 | 30 |
To update the Price of all products to 50, you would run the following query:
UPDATE Products
SET Price = 50;
Result:
After running the query, the Products table will look like this:
| ProductID | Price |
|---|---|
| P1 | 50 |
| P2 | 50 |
| P3 | 50 |
Explanation:
Here, the Price of all products was updated to 50 because the WHERE clause was omitted.
Common Mistakes
- Forgetting the WHERE clause: If you forget to include the
WHEREclause in theUPDATEstatement, all records in the table will be updated. Always double-check your query before running it to avoid updating the entire table by mistake. - Not backing up data: It’s always a good idea to backup your data before running an
UPDATEstatement, especially if you are updating multiple records or working on a production database.
FAQs
Can I update multiple columns in a single UPDATE statement?
Yes, you can update multiple columns in a singleUPDATEstatement by separating the column-value pairs with commas in theSETclause. For example:SET column1 = value1, column2 = value2, ....Can I update multiple tables in a single UPDATE statement?
Some database systems like MySQL and PostgreSQL allow updating multiple tables in a singleUPDATEstatement using theJOINclause. However, this is not supported by all database systems, so you should check the documentation of the database system you are using.
Practice Questions
- Write an SQL query to update the
Priceof all products in theProductstable to 100 if thePriceis less than 50. - Write an SQL query to update the
CustomerNamein theOrderstable using theCustomerNamefrom theCustomerstable. - Write an SQL query to update the
OrderDateof all orders in theOrderstable to the current date. - Write an SQL query to increase the
Quantityof all orders in theOrderDetailstable by 10% if theOrderIDis less than or equal to 10.
Conclusion
The SQL UPDATE statement modifies existing records in a table, allowing updates to single or multiple columns and rows, using date-time functions, subqueries, or JOIN statements. It can also update a table with data from another table. Always double-check your query and backup data before running an UPDATE statement, especially in a production database.
Mastering the UPDATE statement requires regular practice and real-world application. This guide is a starting point, but hands-on experience will solidify your understanding.
_Happy Querying !!_
