Updating Data with SQL

Data changes over time, and the UPDATE statement lets you modify values that already exist in a table. Used carefully it is simple and powerful — but used carelessly it can change far more than you intended, so the filtering condition is essential.

The UPDATE statement

An UPDATE names the table, sets one or more columns to new values, and uses a WHERE clause to choose which rows to change:

UPDATE customers
SET city = 'Houston'
WHERE id = 2;

This changes the city to Houston for the single customer whose id is 2.

Always include a WHERE clause

This is the most important point in the lesson. If you leave out the WHERE clause, the update applies to every row in the table:

UPDATE customers
SET city = 'Houston';   -- changes the city of ALL customers

Before running an update, it is good practice to run the same condition as a SELECT first, so you can see exactly which rows will be affected.

Updating several columns

Separate each column assignment with a comma to change more than one value at once:

UPDATE customers
SET city = 'Reno', state = 'NV'
WHERE id = 1;

Related commands

Two related statements round out basic data changes: INSERT adds new rows to a table, and DELETE removes rows. Like UPDATE, DELETE relies on a WHERE clause to limit which rows it affects.