SQL UPDATE Statement Explained with Examples

The UPDATE statement is used to modify existing records in a table by changing the values of one or more columns.

SQL UPDATE 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:

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:

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.

✖️ Example without WHERE Clause

Without the WHERE clause, all records in the table would have been updated.

UPDATE Students
SET Age = 23;

Results:

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:

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:

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:

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:

OrderDetails table:

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:

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:

Orders table:

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:

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:

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:

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:

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:

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 WHERE clause in the UPDATE statement, 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 UPDATE statement, 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 single UPDATE statement by separating the column-value pairs with commas in the SET clause. 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 single UPDATE statement using the JOIN clause. However, this is not supported by all database systems, so you should check the documentation of the database system you are using.

Practice Questions

1. Write an SQL query to update the Price of all products in the Products table to 100 if the Price is less than 50.
2. Write an SQL query to update the CustomerName in the Orders table using the CustomerName from the Customers table.
3. Write an SQL query to update the OrderDate of all orders in the Orders table to the current date.
4. Write an SQL query to increase the Quantity of all orders in the OrderDetails table by 10% if the OrderID is 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.

If you want to learn more, read the article about: DELETE Statement. ISERT Statement WHERE Clause JOINS Explained

Related Resources

• SQL UPDATE - W3Schools
• UPDATE Statement - MySQL Docs
• SQL Tutorial - GeeksforGeeks

_Happy Querying !!_