--- title: 'Updating and Deleting' source: 'https://academia.sh/en/courses/sql-fundamentals/updating-and-deleting' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:51+00:00' license: 'CC BY-SA 4.0' --- # Updating and Deleting The structure of UPDATE and DELETE statements, what happens when the condition clause is forgotten, reading the number of affected rows, the habit of opening a transaction and rolling back, and how deletion affects related rows. 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." ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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.