04.1 INSERT, UPDATE and DELETE

01 | QUERIES CAN CHANGE STORED FACTS

SELECT builds a result without changing the source. INSERT, UPDATE and DELETE deliberately change data. Each has a different purpose: add a record, edit selected fields or remove a record.

We will practise only on a fresh in-memory database. Predict the intended rows first, then inspect them after the operation. This is a practical workflow, not a substitute for appropriate access controls in a real application.

Use this section’s fresh library practice database. Members stores borrowers, Books stores physical copies , and Loans stores borrowing events. Two copies may share a title but have different IDs and barcodes.

import sqlite3

# Explicit SQL transaction control; fresh data on every run.
connection = sqlite3.connect(":memory:", isolation_level=None)
connection.execute("PRAGMA foreign_keys = ON")
setup_sql = """

CREATE TABLE IF NOT EXISTS Members (
    member_id INTEGER PRIMARY KEY,
    member_name TEXT NOT NULL CHECK (length(trim(member_name)) > 0)
);
CREATE TABLE IF NOT EXISTS Books (
    book_id INTEGER PRIMARY KEY,
    barcode TEXT NOT NULL UNIQUE CHECK (length(trim(barcode)) > 0),
    title TEXT NOT NULL CHECK (length(trim(title)) > 0)
);
CREATE TABLE IF NOT EXISTS Loans (
    loan_id INTEGER PRIMARY KEY,
    member_id INTEGER NOT NULL REFERENCES Members(member_id),
    book_id INTEGER NOT NULL REFERENCES Books(book_id),
    borrowed_on TEXT NOT NULL,
    due_on TEXT NOT NULL,
    returned_on TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS one_active_loan_per_book
ON Loans(book_id) WHERE returned_on IS NULL;

INSERT INTO Members VALUES (1, 'Maya'), (2, 'Leo'), (3, 'Amina');
INSERT INTO Books VALUES (10, 'LIB-010', 'The Code Book'),
    (11, 'LIB-011', 'The Code Book'), (12, 'LIB-012', 'Python Adventures');
INSERT INTO Loans VALUES
    (100, 1, 10, '2026-10-01', '2026-10-15', NULL),
    (101, 2, 11, '2026-09-01', '2026-09-15', '2026-09-14');

"""
changes = """
-- Put this exercise's INSERT, UPDATE, DELETE or transaction here.
"""
query = """
SELECT * FROM Loans ORDER BY loan_id;
"""

try:
    connection.executescript(setup_sql)
    connection.executescript(changes)
    cursor = connection.execute(query)
    print(" | ".join(column[0] for column in cursor.description))
    for row in cursor.fetchall():
        print(row)
except sqlite3.Error as error:
    if connection.in_transaction:
        connection.execute("ROLLBACK")
    print("Database error:", error)
finally:
    connection.close()

02 | READ THE STARTING STATE

Loan 100 is active because returned_on is NULL. Loan 101 is historical because its return date is recorded. Book 12 has never been borrowed in this starting dataset.

03 | INSERT A NEW RECORD

INSERT INTO Members (member_id, member_name)
VALUES (4, 'Noah');

The column list defines how values map into fields. Text uses single quotes. An omitted INTEGER PRIMARY KEY can be assigned by SQLite, but explicit IDs make these small teaching examples easy to follow.

Use clear column lists rather than depending on all-column positional order. A schema change can make an unexplained INSERT VALUES list difficult to maintain.

04 | ADD RELATED DATA IN A VALID ORDER

INSERT INTO Loans (loan_id, member_id, book_id, borrowed_on, due_on)
VALUES (102, 3, 12, '2026-10-10', '2026-10-24');

The referenced member and physical copy already exist. returned_on is omitted and becomes NULL, indicating an active loan.

With foreign keys enabled, an unknown member ID fails rather than creating a dangling reference. The unique partial index in this schema also prevents a second active loan for the same copy.

05 | UPDATE A SPECIFIC ROW

UPDATE Members
SET member_name = 'Maya Chen'
WHERE member_id = 1;

UPDATE names the table; SET names the new field values; WHERE selects rows. The identifier remains unchanged, so existing loans still reference the same member.

Use stable IDs when targeting a person. Names can repeat. An UPDATE that matches no records can succeed with zero affected rows; that is not proof the intended change happened.

06 | CHANGE MORE THAN ONE FIELD

UPDATE Loans
SET due_on = '2026-10-22', returned_on = NULL
WHERE loan_id = 100;

Separate SET assignments with commas, not AND. This example explicitly retains the active status while changing the due date.

The SQL schema stores dates as text here. It does not by itself validate a real calendar date or every date relationship; the project application adds date validation.

07 | PREVIEW THE SAME CONDITION

SELECT * FROM Members WHERE member_id = 1;

UPDATE Members
SET member_name = 'Maya Chen'
WHERE member_id = 1;

Previewing helps catch a mistaken condition. In a multi-user system, data could change between separate steps; a transaction and appropriate database rules may be needed for the complete operation.

Without WHERE, UPDATE targets every row. Sometimes a whole-table change is intended, but it must be deliberate. Do not infer that SET applies to only the first visible record.

08 | DELETE REMOVES ROWS

DELETE FROM Books
WHERE book_id = 12;

This removes the unreferenced physical copy, not merely its title value. The Books table and its columns remain.

DELETE without WHERE targets all rows, subject to constraints. DROP TABLE removes the table itself. Recording a book’s return should normally update the loan, not delete the history.

09 | REFERENCES CAN BLOCK DELETION

Try DELETE FROM Books WHERE book_id = 10 against a fresh setup. It fails because loan 100 references that copy. The default immediate foreign-key behaviour in our schema protects the reference.

Historical loans also reference books. Returning a book does not make its parent row unreferenced. Decide how to archive or retire records rather than remove constraints to force deletion.

UPDATE Loans SET returned_on = '2026-10-12'
WHERE loan_id = 100 AND returned_on IS NULL;

10 | PRACTICE AND CHECK

INSERT INTO Books (book_id, barcode, title)
VALUES (13, 'LIB-013', 'Database Detectives');

Explain why the target condition matters, what is preserved and which database rules could reject the change. Next, we will group related changes into transactions.

KEEP LEARNING

Previous page 04.2 Transactions, COMMIT and ROLLBACK

References: Python SQLite parameters and transactions · SQLite transactions · Partial indexes