Updating and Deleting

Data is in the table. The next two statements change it and delete it. Their syntax is among the shortest in the course, and that is exactly where the danger comes from. The WHERE clause is optional — meaning an update that forgot its condition is syntactically flawless, is accepted by the engine without a warning, and changes every row in the table.

Every example in this lesson runs against the :memory: database: a temporary database built in memory that disappears when the command ends. The way to see what a destructive statement does is to run it, but the place it runs is not real data.

Conditional Update

The UPDATE statement has three parts: which table, which columns get which values, and which rows. If the third part is left out, the answer to “which rows” becomes “all of them.”

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  status  TEXT NOT NULL DEFAULT 'active'
           CHECK (status IN ('active', 'suspended', 'closed'))
);
INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','active'),
                       (3,'Clara','Diaz','suspended'),(4,'Derek','Voss','active');

SELECT member_id, first_name, last_name FROM member WHERE member_id = 2;

UPDATE member SET status = 'suspended' WHERE member_id = 2;
SELECT changes() AS affected;

SELECT * FROM member;
SQL

member_id  first_name  last_name
---------  ----------  ---------
2          Ben         Ortiz    
affected
--------
1       
member_id  first_name  last_name  status   
---------  ----------  ---------  ---------
1          Alice       Kane       active   
2          Ben         Ortiz      suspended
3          Clara       Diaz       suspended
4          Derek       Voss       active   

Two habits are built into this block. The first is running a SELECT with the same condition before the update: which rows will be affected gets seen first. The second is reading the number of affected rows after the update. If the expected number is known — here, one — a deviation is caught immediately.

An update does not skip constraints. An update that writes a value to the status column that is not on the list produces the same CHECK error as an insert would. Constraints in the schema hold across every write path.

Forgetting the Condition

The block below shows what an update with no condition does. The statement itself is identical to the previous one, only the WHERE clause is missing.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  status  TEXT NOT NULL DEFAULT 'active'
           CHECK (status IN ('active', 'suspended', 'closed'))
);
INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','active'),
                       (3,'Clara','Diaz','suspended'),(4,'Derek','Voss','active');

UPDATE member SET status = 'suspended';
SELECT changes() AS affected;

SELECT * FROM member;
SQL

affected
--------
4       
member_id  first_name  last_name  status   
---------  ----------  ---------  ---------
1          Alice       Kane       suspended
2          Ben         Ortiz      suspended
3          Clara       Diaz       suspended
4          Derek       Voss       suspended

Four rows. No error, no warning, no confirmation prompt. Every member of the library was suspended, and the only thing that says so is the number in the changes() output.

The scale of the damage is proportional to the size of the table. In a four-row example, the result is seen in a second; on a member table with four hundred thousand rows, it turns into the question of where the old values are kept. The old values are not kept anywhere — the update happens in place. The way back is restoring from a backup, and that only goes as far back as the moment the backup was taken.

The same holds for deletion, and there it cuts even sharper: an unconditional DELETE removes every row in the table.

A Reversible Habit

The answer to this risk is not care, it is a procedure. The write statement runs inside an explicit transaction; the number of affected rows is read; if the number matches expectations, the transaction is committed, and if not, it is rolled back.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  status  TEXT NOT NULL DEFAULT 'active'
);
INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','active'),
                       (3,'Clara','Diaz','suspended'),(4,'Derek','Voss','active');

BEGIN;
UPDATE member SET status = 'suspended';
SELECT changes() AS affected;
ROLLBACK;

SELECT * FROM member;
SQL

affected
--------
4       
member_id  first_name  last_name  status   
---------  ----------  ---------  ---------
1          Alice       Kane       active   
2          Ben         Ortiz      active   
3          Clara       Diaz       suspended
4          Derek       Voss       active   

The same unconditional update ran, affected the same four rows, and then the ROLLBACK statement undid all of it. The table is in its pre-transaction state: the third member suspended, the rest active.

A transaction that begins with BEGIN is not durable until a COMMIT is seen. This should be the default frame for every write statement written by hand. COMMIT is written if the statement is correct, ROLLBACK if the number is surprising — and the chance to see a surprising number exists only while the transaction is still open.

The detail of transactions — isolation levels, locking, partial rollback — is the subject of the Advanced SQL course. All that is needed here is the frame.

Taking the Condition From a Query

The rows to update can be determined by a query’s result. This makes it possible to write a rule like “suspend members with an overdue, unreturned loan” in a single statement.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  status TEXT NOT NULL DEFAULT 'active');
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
INSERT INTO member VALUES (1,'Alice','Kane','active'),(2,'Ben','Ortiz','active'),
                       (3,'Clara','Diaz','active');
INSERT INTO loan VALUES
  (1, 1, 1, '2024-11-01', NULL),
  (2, 2, 2, '2025-06-01', NULL),
  (3, 3, 3, '2024-12-15', '2025-01-02');

BEGIN;
UPDATE member
SET status = 'suspended'
WHERE member_id IN (SELECT member_id FROM loan
                 WHERE return_date IS NULL AND pickup_date < '2025-01-01');
SELECT changes() AS affected;
COMMIT;

SELECT * FROM member;
SQL

affected
--------
1       
member_id  first_name  last_name  status   
---------  ----------  ---------  ---------
1          Alice       Kane       suspended
2          Ben         Ortiz      active   
3          Clara       Diaz       active   

Only the first member was suspended: the second has an open loan but a recent date, the third’s loan is old but has been returned. The subquery applied both conditions together.

A side benefit of this syntax is that it can be tested: the subquery can be run on its own as a SELECT before the update, and which ids come back can be seen. A detailed treatment of subqueries belongs to the Advanced SQL course; here their use is limited to writing conditions.

Deletion and Related Rows

The structure of the DELETE statement is even shorter: which table and which rows.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
INSERT INTO loan VALUES
  (1,1,1,'2023-01-10','2023-01-24'),
  (2,2,1,'2023-02-02','2023-02-20'),
  (3,1,2,'2025-02-11',NULL),
  (4,3,3,'2025-03-01','2025-03-15');

DELETE FROM loan WHERE return_date IS NOT NULL AND return_date < '2024-01-01';
SELECT changes() AS deleted;

DELETE FROM loan;
SELECT changes() AS deleted;
SELECT count(*) AS remaining FROM loan;
SQL

deleted
-------
2      
deleted
-------
2      
remaining
---------
0        

The second statement had no condition, and the table emptied out. This is why delete statements are written with an even stricter procedure than updates: the condition is counted first with SELECT count(*), then the delete runs inside a transaction.

Deleting a row also concerns the rows tied to it. The behavior attached to the foreign key definition decides what the engine does. Under restrictive behavior, the delete is rejected.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
PRAGMA foreign_keys = ON;
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL);
CREATE TABLE loan (
  loan_id       INTEGER PRIMARY KEY,
  book_id       INTEGER NOT NULL,
  member_id     INTEGER NOT NULL REFERENCES member(member_id) ON DELETE RESTRICT,
  pickup_date TEXT NOT NULL
);
INSERT INTO member VALUES (1,'Alice','Kane'),(2,'Ben','Ortiz');
INSERT INTO loan VALUES (1,1,1,'2025-02-11');

DELETE FROM member WHERE member_id = 1;
DELETE FROM member WHERE member_id = 2;
SELECT changes() AS deleted;
SELECT * FROM member;
SQL

Runtime error near line 14: FOREIGN KEY constraint failed (19)
deleted
-------
1      
member_id  first_name  last_name
---------  ----------  ---------
1          Alice       Kane     

The member with a loan could not be deleted; the one without was. Under cascading behavior, related rows are removed too.

sqlite3 :memory: <<'SQL'
.headers on
.mode column
PRAGMA foreign_keys = ON;
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL);
CREATE TABLE loan (
  loan_id       INTEGER PRIMARY KEY,
  book_id       INTEGER NOT NULL,
  member_id     INTEGER NOT NULL REFERENCES member(member_id) ON DELETE CASCADE,
  pickup_date TEXT NOT NULL
);
INSERT INTO member VALUES (1,'Alice','Kane'),(2,'Ben','Ortiz');
INSERT INTO loan VALUES (1,1,1,'2025-01-10'),(2,2,1,'2025-02-02'),(3,1,2,'2025-02-11');

DELETE FROM member WHERE member_id = 1;
SELECT changes() AS deleted_members;
SELECT * FROM loan;
SQL

deleted_members
---------------
1              
loan_id  book_id  member_id  pickup_date
-------  -------  ---------  -----------
3        1        2          2025-02-11 

A single member was deleted, and two loan records went with it. The affected-row count still reads 1: the counter looks at the table the statement directly targets, and does not count cascading deletes. Choosing cascading behavior means accepting that loan history disappears along with the member. In domains where history has value, the way library records do, marking a row closed — giving the member a closed status — is a better design than deleting the row.

Summary

• In UPDATE and DELETE statements the condition clause is optional; when it is left out, the statement affects every row in the table and the engine gives no warning.
• The number of affected rows is read after every write statement; a deviation from the expected number is the only early sign of a wrong condition.
• Write statements written by hand run inside a transaction: they are committed if the number is correct and rolled back if it is not.
• The rows to update can be determined by a subquery; the subquery is tested by running it on its own before the statement.
• The effect of a delete on related rows is written into the foreign key definition; because a cascading delete takes the history with it, closing a record is preferred in domains where records have value.

Next Step

The statements so far have run directly on tables. The next lesson inserts a layer in between: views, which name a query and get used like a table. A view does not duplicate data, it stores the query — and that raises an interesting question: can a row be inserted into a view, can it be updated. The answer depends on how the view is defined and on the engine; part of that lesson shows this by trying it.