🚀 Think you’ve got what it takes for a career in Data? Find out in just one minute!

SQL UPDATE: Update data with this command

-
3
 m de lecture
-
UPDATE SQL : Update data with this command

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 E-mail
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 E-mail
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 E-mail
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 E-mail
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:

SQL DELETE: How do I use this query?
SQL Developer: What is it? What’s it for?
SQL Tutorial: Top 5 Most Useful Methods
SQL joins: everything you need to know about table associations
SQL vs NoSQL: differences, uses, advantages and disadvantages
NoSQL: All about non-relational databases

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.

Facebook
Twitter
LinkedIn

DataScientest News

Sign up for our Newsletter to receive our guides, tutorials, events, and the latest news directly in your inbox.

You are not available?

Leave us your e-mail, so that we can send you your new articles when they are published!
icon newsletter

DataNews

Get monthly insider insights from experts directly in your mailbox