Update Data

UPDATE modifies existing rows.

It is powerful and risky: without a WHERE clause, every row in the table may change.

Core Concepts

flowchart LR
  A[UPDATE table] --> B[SET new values]
  B --> C{WHERE condition?}
  C -->|Yes| D[Only matching rows change]
  C -->|No| E[All rows change]

Component	Example
Target table	UPDATE users
New values	SET active = 0
Filter	WHERE email = 'x@example.com'

Code Examples

-- Setup data for update demo.
CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  active    INTEGER NOT NULL DEFAULT 1
);

INSERT INTO members (name, active)
VALUES ('Ada', 1), ('Alan', 1), ('Linus', 1);

Expected output
Table created and three rows inserted.

-- Deactivate one specific member.
UPDATE members
SET active = 0
WHERE name = 'Alan';

Expected output
One row is updated.

-- Verify current state.
SELECT member_id, name, active
FROM members
ORDER BY member_id;

member_id	name	active
1	Ada	1
2	Alan	0
3	Linus	1

SQLite-Specific Nuances

SQLite Nuance

SQLite uses the same UPDATE ... SET ... WHERE ... pattern as other major SQL systems, so this skill transfers well.

Common Pitfalls / Best Practices

Pitfall

Forgetting the WHERE clause and updating every row unintentionally.

Best Practice

Before running a critical update, run a SELECT with the same WHERE clause to preview affected rows.

Quick Challenge

Set active = 0 for Linus only.

View Solution

UPDATE members
SET active = 0
WHERE name = 'Linus';

Expected output
Exactly one row changes (Linus).