Because databases are constantly evolving, it's essential to be familiar with the UPDATE SQL query. This allows you to modify one or more rows in your table. So how do you use it? We explain it all in this article.
What is SQL UPDATE?
The SQL UPDATE command is used to update data within a table. It’s essential if you want to maintain a database with up-to-date, reliable information.
As data quality is one of the biggest challenges facing data scientists, UPDATE is one of the most common SQL queries.
How do I use the UPDATE SQL command?
The syntax of the UPDATE SQL query is as follows:
UPDATE table name
SET column name = ‘new value
WHERE condition
To better understand it, let’s take a look at its main attributes:
The SET clause: this defines what you’re going to replace existing data with. Here, only the columns to be modified need be specified. All other columns retain their current values.
The WHERE clause: this specifies where the changes are to be made.
Beyond this simplified syntax, it’s also possible to use the UPDATE function with other SQL queries, such as SELECT, INSERT INTO, GETDATE, INNER, JOIN and so on. This allows you to enrich your database and perform more complex manipulations.
5 examples of UPDATE queries
Modify a line
In this example, we use the Customer table below.
| ID | First name | Last name | City | |
|---|---|---|---|---|
| 1 | Justin | Martin | Paris | justinemartin@gmail.com |
| 2 | Thomas | Durant | Bordeaux | tomtom@sfr.fr |
| 3 | Marie | Leroy | Angers | marieleroy@laposte.net |
| 4 | Vanessa | Savary | Marseille | vanessa13@gmail.com |
Here, you want to update Justine Martin’s email address. Here’s her new address: justinemartin@sfr.fr. To do this, simply enter the following query:
UPDATE Customer
SET Email = ‘justinemartin@sfr.fr’
WHERE id = 1
And here’s the result:
| ID | First name | Last name | City | |
|---|---|---|---|---|
| 1 | Justin | Martin | Paris | justinemartin@sfr.fr |
| 2 | Thomas | Durant | Bordeaux | tomtom@sfr.fr |
| 3 | Marie | Leroy | Angers | marieleroy@laposte.net |
| 4 | Vanessa | Savary | Marseille | vanessa13@gmail.com |
Modify multiple data
If you wish to modify several pieces of information on the same line, simply separate the new values with a comma.
In our example, you’d like to change Not only Justine Martin’s email address, but also her city. She now lives in Nantes.
Here’s the query:
UPDATE Customer
SET
City = ‘Nantes’,
Email = ‘justinemartin@sfr.fr’
WHERE id = 1
And the new table:
| ID | First name | Last name | City | |
|---|---|---|---|---|
| 1 | Justin | Martin | Nantes | justinemartin@sfr.fr |
| 2 | Thomas | Durant | Bordeaux | tomtom@sfr.fr |
| 3 | Marie | Leroy | Angers | marieleroy@laposte.net |
| 4 | Vanessa | Savary | Marseille | vanessa13@gmail.com |
Modify all lines
If you want to modify all the rows in the table, simply omit the WHERE query. For example, if all customers are moving to Lyon, here’s the query to save:
UPDATE Customer
SET
City = ‘Lyon
And the new table:
| ID | First name | Last name | City | |
|---|---|---|---|---|
| 1 | Justin | Martin | Lyon | justinemartin@sfr.fr |
| 2 | Thomas | Durant | Lyon | tomtom@sfr.fr |
| 3 | Marie | Leroy | Lyon | marieleroy@laposte.net |
| 4 | Vanessa | Savary | Lyon | vanessa13@gmail.com |
Modify data from another table
For this example, we use two tables.
The first, “Products”, lists the products sold by a company and the salesperson who made the sale:
| Product | Price | Commercial ID | Salesperson name |
|---|---|---|---|
| Battery | 100 | 5 | NULL |
| Telephone | 300 | 1 | NULL |
| Computer | 800 | 3 | NULL |
The second “Employee” lists a company’s employees.
| ID | Price | Commercial ID |
|---|---|---|
| 1 | Justin | Martin |
| 2 | Thomas | Durant |
| 3 | Marie | Leroy |
| 4 | Vanessa | Savary |
| 5 | Jean | Dupont |
The aim is to identify the name of the sales rep who made the sale, based on the sales rep’s ID. This name will then be integrated into the product table.
To achieve this, here’s the query to record:
UPDATE Product
SET Salesperson name = (SELECT “name” from “Employee” WHERE salesperson ID = ID)
And here is the result:
| ID | Product | Price | Commercial ID | Salesperson name |
|---|---|---|---|---|
| 1 | Battery | 100 | 5 | Dupont |
| 2 | Telephone | 300 | 1 | Martin |
| 3 | Computer | 800 | 3 | Leroy |
Update time with SQL UPDATE
You can also use the SQL query UPDATE with the GETDATE command to update the time and date.
For example, with this “Product” table, you’d like to update the purchase date of the products below to today’s date (04/25/2023).
| ID | Product | Price | Purchase date |
|---|---|---|---|
| 1 | Battery | 100 | 01/03/2023 |
| 2 | Telephone | 300 | 06/03/2022 |
| 3 | Computer | 800 | 15/04/2023 |
To do this, enter the following query:
UPDATE Product
SET purchase date = GETDATE()
The table is as follows:
| ID | Product | Price | Purchase Date |
|---|---|---|---|
| 1 | Battery | $100 | 25/04/2023 |
| 2 | Phone | $300 | 25/04/2023 |
| 3 | Computer | $800 | 25/04/2023 |
As you can see, the SQL UPDATE query can be used in a multitude of ways. A single blog post may not be enough to cover the wide range of possible uses. If you’d like to find out more, don’t hesitate to join one of our training courses at DataScientest.
💡Related articles:
Expand your knowledge of SQL
While SQL is the indispensable basis for manipulating relational databases, mastering it requires training and many hours of practice. That’s why we offer a range of training courses at DataScientest. Each course is tailored to your specific needs, either in terms of business or tools.
