Updating Data in a Database Table

Introduction

One of the most powerful features of databases is the ability to update data efficiently. Whether it’s updating a user’s profile information or marking an invoice as paid, the UPDATE statement enables you to modify existing records. In this guide, you’ll learn how to:

Write and execute UPDATE statements in SQL.
Use WHERE clauses to target specific rows for updates.
Avoid common pitfalls and errors when updating data.

Setting Up the Example Table

For this guide, let’s use a table named friends. It has the following schema:

Name	Data Type	Constraints
id	INTEGER	PRIMARY KEY AUTOINCREMENT
first_name	VARCHAR(255)	NOT NULL
last_name	VARCHAR(255)	NOT NULL

Create the table using the following SQL:

CREATE TABLE friends (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL
);

Populating the Table

Insert the following rows to populate the table:

id	first_name	last_name
1	Amy	Pond
2	Rose	Tyler
3	Martha	Jones
4	Donna	Noble
5	River	Song

Use the following SQL to insert the data:

INSERT INTO friends (first_name, last_name)
VALUES
  ('Amy', 'Pond'),
  ('Rose', 'Tyler'),
  ('Martha', 'Jones'),
  ('Donna', 'Noble'),
  ('River', 'Song');

Updating a Row in a Database Table

To update a row, you need to specify:

The table to update.
The column(s) to modify and the new value(s).
The WHERE clause to filter the rows to update.

Example: Updating a Friend’s Last Name

Imagine your friend Amy Pond gets married and changes her last name to Blue. Here’s how you update her record:

UPDATE friends
SET last_name = 'Blue'
WHERE first_name = 'Amy' AND last_name = 'Pond';

After running the UPDATE statement, verify the changes using:

SELECT * FROM friends;

Handling Silent Failures

If no rows match the WHERE clause, the UPDATE statement will fail silently, meaning it won’t generate an error, and the table remains unchanged.

Tip:

Use a SELECT query with the same WHERE clause beforehand to ensure you are targeting the correct rows:

SELECT * FROM friends
WHERE first_name = 'Amy' AND last_name = 'Pond';

Common Errors When Updating Data

Updating data may result in errors if constraints are violated. For example:

Error: Violating a UNIQUE Constraint

If you try to update the id column to a value that already exists:

# BAD - DO NOT DO THIS!
UPDATE friends
SET id = 2
WHERE id = 1;

SQLite will throw the following error:

Error: UNIQUE constraint failed: friends.id

This happens because id is a primary key and must be unique.

Best Practices for UPDATE Statements

Always use a WHERE clause to target specific rows.
Test the WHERE clause with a SELECT statement first to ensure accuracy.
Double-check constraints and unique columns to avoid errors.
Back up your database before running bulk updates, especially in production environments.

What You Learned

How to use the UPDATE statement to modify rows in a database table.
The importance of the WHERE clause for targeting specific rows.
Common errors and how to avoid them.

The UPDATE statement is a fundamental tool for managing and modifying data in a database. Practice using it to gain confidence in its syntax and usage.