Basic

Changing one row

I needed to fix one value and I deleted the row and added it back. That worked but it was clumsy, and it lost any column I had forgotten about. UPDATE changes a value in place.

What you will see

The full result is shown at the end of the exercise below.

About 2 min of reading

Try It

Fix a value in place, then see what happens when you forget WHERE.

Change one row's name. Only id 2 is touched.

UPDATE users SET name = "Grace H." WHERE id = 2;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Read that row back to confirm the new name landed.

SELECT name FROM users WHERE id = 2;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

name Grace H. sqlite>

Now change a column with no WHERE. Every row's name becomes the same text. This is the accident, run it on your own small table to see it.

UPDATE users SET name = "anon";

Before you run it, say out loud what it will print. Then open the answer.

What it prints

sqlite>

Read the whole table. Every name now reads anon, which is why WHERE is not optional when you mean one row.

SELECT * FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints

Data Flow

UPDATE names a table, SET gives new values, WHERE picks rows, and only matching rows change in place.

1 UPDATE names the table

↓

2 SET gives new values

↓

3 WHERE picks the rows

↓

4 Matching rows change

Why this works

UPDATE has three parts

UPDATE names the table, SET lists the column and its new value, WHERE picks the rows. Drop the WHERE and you meant one row but you changed all of them. This is the single most common accident in SQL, and it is why you reach for WHERE even when you think the table is small.

Change several columns

You can set more than one column in one UPDATE by separating them with commas:

UPDATE users SET name = "G.", id = 9 WHERE id = 2;

Both changes land on the same row in one step. The WHERE is checked once, against the row as it was before any change.

Examples

Change a value

UPDATE users UPDATE users WHERE id = 2;

That is not valid. UPDATE names the table, SET says the new value, WHERE says which row. All three parts, and the WHERE is the one that saves the other rows.

The real shape

UPDATE users SET name = "Grace H." WHERE id = 2;

Only the row where id is 2 changes. The name in that row becomes the new text. Every other row is left exactly as it was.

No WHERE is dangerous

UPDATE users SET name = "anon";

Without WHERE this changes the name in every row. The database does not warn you, it just does it. When you mean one row, always write WHERE.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Adding rows safely

Previous Lesson

Adding rows safely

Next Lesson

Counting instead of listing

Counting instead of listing →