SQL UPDATE Statement

Learn how to use SQL UPDATE to change existing table data, target rows with WHERE, update multiple columns, verify changes, and avoid accidental updates.

The UPDATE statement changes values that are already stored in existing table rows. It can modify one row, several matching rows, or every row in a table.

Use INSERT to add new rows, DELETE to remove rows, and SELECT to read data without changing it.

Basic UPDATE Syntax

The general pattern is:

UPDATE table_name
SET column_name = value
WHERE condition;

UPDATE table_name identifies the table containing the rows to modify. The SET clause contains one or more assignments. An assignment connects a column to its replacement value, such as email = 'john@email.com'. The WHERE clause filters the rows eligible for the change. The semicolon terminates the statement in most SQL environments.

UPDATE Clause Reference

UPDATE table_name — selects the target table.

SET column_name = value — specifies the new value for a column.

WHERE condition — selects the rows to update.

Using WHERE to Target Rows

WHERE is essential because it determines which rows are changed. A condition can compare one column:

UPDATE employee
SET email = 'john@email.com'
WHERE employeeNumber = 0;

Multiple conditions can be joined with AND. Every condition must be true for a row to match:

UPDATE employee
SET email = 'john@email.com'
WHERE firstName = 'John'
  AND lastName = 'Doe';

If you omit WHERE, the database updates every row in the target table:

UPDATE employee
SET email = 'unknown@example.com';

Updating a Single Column

Suppose an employee table contains these records. John Doe currently has a NULL email value, meaning no email value is stored.

Before update
employeeNumber | lastName  | firstName | extension | email
0              | Doe       | John      | x1234     | NULL
1              | Murphy    | Diane     | x5800     | dmurphy@example.com
2              | Patterson | Mary      | x4611     | mpatterson@example.com
3              | Firrelli  | Jeff      | x9273     | jfirrelli@example.com
4              | Patterson | William   | x1024     | wpatterson@example.com
5              | Bondur    | Gerard    | x5408     | gbondur@example.com

Update only John Doe's email with a condition based on both identifying columns:

UPDATE employee
SET email = 'john@email.com'
WHERE firstName = 'John'
  AND lastName = 'Doe';

After the statement, the other columns and other employee rows remain unchanged:

After update
employeeNumber | lastName  | firstName | extension | email
0              | Doe       | John      | x1234     | john@email.com
1              | Murphy    | Diane     | x5800     | dmurphy@example.com
2              | Patterson | Mary      | x4611     | mpatterson@example.com
3              | Firrelli  | Jeff      | x9273     | jfirrelli@example.com
4              | Patterson | William   | x1024     | wpatterson@example.com
5              | Bondur    | Gerard    | x5408     | gbondur@example.com

Updating Multiple Columns

Place multiple comma-separated assignments in one SET clause. All assignments apply to each row matched by the same WHERE condition:

UPDATE employee
SET email = 'john@email.com',
    extension = 'x233333'
WHERE employeeNumber = 0;

This statement changes both the email and extension for employee number 0. It does not change rows that do not match the condition.

Verifying an UPDATE

First preview the rows that the condition will select:

SELECT *
FROM employee
WHERE firstName = 'John'
  AND lastName = 'Doe';

After updating, run a SELECT with the same identifying condition to confirm the new values:

SELECT employeeNumber, firstName, lastName, email
FROM employee
WHERE firstName = 'John'
  AND lastName = 'Doe';

Many database clients also report an affected-row count. Depending on the database system and its settings, this may mean rows changed or rows matched by the update. A count of zero usually means that no row satisfied the condition, although the exact reporting behavior varies by system.

Use a Primary Key for Safer Updates

A primary key is a column, or set of columns, that uniquely identifies a row. When possible, use it in the WHERE clause:

UPDATE employee
SET email = 'john@email.com'
WHERE employeeNumber = 0;

A person's first name and last name may not be unique. Two employees can both be named John Doe, so a condition using those names could update multiple rows. An employee number that is a primary key identifies one unambiguous record.

Transactions, Rollback, and Backups

For important changes, use a transaction when the database supports transactional updates. A transaction is a unit of database work that can be committed or rolled back. Review the result before making it permanent:

BEGIN;

UPDATE employee
SET email = 'john@email.com'
WHERE employeeNumber = 0;

SELECT *
FROM employee
WHERE employeeNumber = 0;

COMMIT;

Use ROLLBACK; instead of COMMIT; if the result is incorrect and the transaction is still open. Transaction behavior and syntax can vary between database systems. Backups provide another layer of protection for important data changes.

Safe UPDATE Checklist

Identify the target table and columns carefully.
Prefer a primary key in the WHERE clause.
Run a SELECT with the intended condition before running UPDATE.
Confirm the affected-row count is what you expect.
Use a transaction for changes that should be reviewed before commit.
Keep a reliable backup before large or important updates.
In application code, use parameterized queries rather than concatenating user input into SQL.

Common UPDATE Problems

Every row was changed

The statement may have omitted WHERE, or its condition may have matched every row. Preview the condition with SELECT, use a more restrictive predicate, and use a transaction so the change can be rolled back before commit.

No rows were updated

The condition may not match the stored values because of spelling, capitalization rules, whitespace, or an incorrect identifier. Run a SELECT using the exact same WHERE clause and inspect the stored data.

More than one employee was updated

A non-unique condition, such as only a first name or a first-name-and-last-name combination, may match multiple rows. Use a primary key such as employeeNumber when one record is intended.

A text value causes a syntax error

String values normally need quotes, and a quote inside the value must be handled according to the target SQL dialect. In application code, use parameterized queries to handle values safely and help prevent SQL injection.

A blank value is not matched

NULL is different from an empty string. To find a NULL value, use IS NULL rather than = NULL:

SELECT *
FROM employee
WHERE email IS NULL;

Key Terms

UPDATE: The SQL command that modifies values in existing table rows.
SET: The clause specifying columns and their new values.
WHERE: The clause filtering rows eligible for modification.
Row: One record in a table.
Column: A named attribute stored for each row.
Assignment: A column-to-new-value expression in the SET clause.
Primary key: A column or column set that uniquely identifies a row.
Affected rows: The number of rows changed or matched, depending on the database system.
Transaction: A unit of database work that can be committed or rolled back when supported.