Decoding the SQL UPDATE Command 

Learn how to use the SQL UPDATE command to update records in a database table.

As a developer, you should be comfortable working with relational databases. SQL (or Structured Query Language) is a beginner-friendly language that is intuitive to learn and has a simple syntax. SQL lets you create database objects and query relational database tables.

You can run SQL queries to create, read, update, and delete records in a database table. You might be familiar that these operations are collectively called CRUD operations. 

To update the records stored in a database table, you can run the UPDATE command in SQL. 

In this tutorial, you’ll learn:

• The syntax of the UPDATE command 
• How to update records based on a specific condition or multiple conditions chained together using logical operators 
• Common caveat to be aware of when running the SQL UPDATE command 

Syntax of the SQL UPDATE Command

The syntax of the SQL UPDATE command is:

UPDATE table_name
SET column = new_value
WHERE condition;

The above query updates column for all records where the condition is true.

To update multiple columns (or fields), use the SQL UPDATE command like so:

UPDATE table_name
SET column_1 = new_value_1, column_2 = new_value_2, ..., column_k = new_value_k
WHERE condition;

Recall that in a relational database:

• The table represents an entity.
• The rows of the table are the records and represent an instance of the entity.
• The columns are also called the fields or attributes. In this tutorial, we’ll use columns and fields interchangeably.

SQL UPDATE Command Usage Examples

Now let’s take some examples.

Prerequisites

Before you start coding:

• This tutorial uses SQLite. So you’ll need to have SQLite and SQLite Database browser (recommended) to code along. Note that you can also use MySQL or PostgreSQL.
• If you want to replicate the example, you need Python and the Faker Python package as well.

Creating a Database Table with Records

If you’d like to code along to this tutorial, then you can run the following code snippet to create and connect to a customer database customer_db.db. Notice that we use the Faker Python library to generate synthetic data to insert into the customers table:

# main.py

import sqlite3
from faker import Faker
import random

# connect to the db
conn = sqlite3.connect('customer_db.db')
cur = conn.cur()

# create a database table
cur.execute('''CREATE TABLE customers (
                  customerID INTEGER PRIMARY KEY,
                  name TEXT,
                  city TEXT,
                  email TEXT,
                  num_orders INTEGER,
                  discount INTEGER DEFAULT 2)''')


# create a Faker object 
fake = Faker()
Faker.seed(42)

for _ in range(15):
    name = fake.name()
    city = fake.city()
    d = fake.domain_name()
    email = f"{name[:2]}.{city[:2]}@{d}"
    num_orders = random.choice(range(200))
    db_cursor.execute('INSERT INTO customers (name, city, email, num_orders) \
    VALUES (?,?,?,?)', (name,city,email,num_orders))

# commit the transaction 
conn.commit()
cur.close()
conn.close()

📑 For this code to work without errors, make sure you have Python 3.7 (or a later version) and Faker installed in your development environment. You can install the Faker Python package using pip:

pip install faker

If you already have a database that you can connect to and a database table that you can query, you can also use it as preferred.

Running Our First SQL Query

The customers table has the following fields:

• customerID: The customerID is the primary key that helps us uniquely identify a record in a database table.
• name: The name of the customer
• city: The city they belong to.
• email: Their email address.
• num_orders: The number of orders they’ve placed.
• discount: The discount percentage, an integer with a default value of 2. Because the discount field has a default value, we do not need to insert a value for it when populating the database table.

📝 You can run the queries and view the results using the SQLite command-line client. Or you can use the SQLite database browser.

I’ll present the outputs from the SQLite DB Browser as they’re easy to interpret.

Run the following query to get all the records in the customers table:

SELECT * FROM customers;

In practice, you should avoid using SELECT * unless it’s necessary. But for this example, we’ll use it as we have only 15 records and not many fields.

Updating Records Based on a Single Condition

Now that we know what our table looks like, let’s run a few UPDATE queries to update the records based on the required condition.

📋 Note: After running the UPDATE statements, we’ll run SELECT * FROM customers to see the updated records.

Updating a Single Field

First, let’s update the city field of the record with customerID=7:

UPDATE customers
SET city='Codeshire'
WHERE customerID=7;

Let’s select all the columns and records from the customers table:

SELECT * FROM customers;

We see that the city field of Danielle Ford (customerID=7) has been updated.

Updating Multiple Fields

In the previous example, we updated only one field, namely, the city of the record corresponding to the customerID 7. But we can also update multiple fields using the syntax we’ve learned.

Here we update both the city and the email field corresponding to the customerID 1:

UPDATE customers
SET city='East Carlisle',email='Al.Ea@isle.com'
WHERE customerID=1;

Next, we run:

SELECT * FROM customers;

And here’s the output:

Updating Multiple Records

Because we used the customerID which is the primary key that uniquely identifies a customer record, the queries that we’ve run thus far updated only one of the records.

However, if the condition is true for more than one record in the table, then running the SQL update command updates all the corresponding records.

Take this query for example:

UPDATE customers 
SET discount=10
WHERE num_orders > 170;

Now run this query:

SELECT * FROM customers;

Here’s the output:

Running the above SQL update command modifies three records. They all have num_orders greater than 170 and now have a discount value of 10.

Updating Records Based on Multiple Conditions 

So far the WHERE clause had a simple condition, but it’s common for the filtering criterion to have multiple conditions chained together by logical operators.

To understand this, let’s set the discount to 5 based on two conditions:

• city LIKE 'New%‘: this condition checks and includes those records where the city field starts with the New, and 
• num_orders > 100 filters based on the number of orders to include only those records for which the number of orders is greater than 100.

The UPDATE statement looks like this:

UPDATE customers
SET DISCOUNT=5
WHERE city LIKE 'New%' AND num_orders>100;

Notice that we now have two conditions in the WHERE clause—chained together by the logical AND operator. And only those records for which both conditions hold true are updated.

Next run this query and observe the output:

SELECT * FROM customers;

As seen in the output, the discount field for those records for which both the above conditions hold true are updated:

Common Caveat When Using the SQL UPDATE Command

In all the UPDATE statements executed so far, we included the WHERE clause.

For example, you’d like to update the discount to 25 for a particular customer. And in ypur update query, you forget to include the WHERE clause with the customerID to filter on:

UPDATE customers
SET DISCOUNT=25;

Now run:

SELECT * FROM customers;

You’ll see that all the records in the table are updated. This may not be the behavior you’d want.

⚠ So remember to include the WHERE clause when you want to update a subset of records based on a condition. If you want to update a particular field for all the records, you can omit the WHERE clause.

In this example, say you have the Black Friday sale coming up, and you want to offer all your customers a discount of 25%. Then running the above query will make sense.

Conclusion

Here’s a summary of what you’ve learned:

• When you want to update records in a database table, you can use the SQL UPDATE command.
• The SQL UPDATE statement typically includes the WHERE clause with the condition that determines the subset of records (rows of the table) to update. 
• Omitting the WHERE clause in the UPDATE statement updates all the records. So you have to be careful not to omit the WHERE clause if updating all the rows of the table is not the desired behavior.

Next, check out this SQL cheat sheet for quick reference.