DbSchema Tutorial | SQL UPDATE STATEMENT

---

SQL Tutorial • 23-Aug-2023

by Malik Umair Sajjad

---

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 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.

_Happy Querying !!_