Concepts / Querying Data with SELECT: Retrieving Information

Querying Data with SELECT: Retrieving Information

Deleting data is different from inserting or updating it.

  • Programming
Interactive lab

Try it: INSERT, UPDATE and DELETE

How INSERT builds a new row from its column list (omitted columns get the generated id, their DEFAULT or NULL), how UPDATE … SET and DELETE test WHERE on every row and change only the matches — or every row when WHERE is missing — and how cur.rowcount and commit() report and keep the change.

How it works

  1. The statement is sent with ? placeholders and a tuple of values: cur.execute(sql, params).
  2. INSERT: the listed columns get the values in order; an unlisted INTEGER PRIMARY KEY gets max(id) + 1, an unlisted column gets its DEFAULT (or NULL). A listed id that already exists raises IntegrityError.
  3. UPDATE / DELETE: WHERE is evaluated on each row; only rows where it is TRUE match (NULL does not). No WHERE means every row matches. The same WHERE in a SELECT shows the target rows first.
  4. UPDATE applies every SET assignment to each matched row, reading the row's old values; DELETE removes the matched rows. Matching nothing is not an error.
  5. cur.rowcount is the number of rows inserted, changed or removed.
  6. The change is pending until conn.commit(); conn.close() without commit() rolls it back, so a new connection still sees the old table.

Default run (11 steps): UPDATE track SET plays = ? WHERE title = ? with parameters (16, 'My Way'). The track table starts with 5 rows. … conn.commit() makes the change permanent. A new connection now sees 5 rows.

Simplified: A toy relational engine in the browser, not SQLite itself: statements are built from the controls (no SQL text is parsed or executed) and tables have a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). One table track(id INTEGER PRIMARY KEY, title TEXT, plays INTEGER DEFAULT 0) of up to 8 rows, one WHERE comparison, text limited to printable ASCII. The generated id is max(id) + 1, SQLite's rule for an INTEGER PRIMARY KEY without AUTOINCREMENT.

Educational simulation

Loading the simulation…

Deletion Requires Deliberate Targeting

Deleting data is different from inserting or updating it. Inserting adds a row, and updating modifies an existing value. DELETE removes rows from a table, so the operation demands careful targeting. In most practical situations, recovering a deleted row requires restoring it from a backup.

Key point

The WHERE clause is the main safety mechanism in a DELETE statement. It acts as a filter that identifies which rows should be removed and which should remain.

Filtering Rows with WHERE

A DELETE statement removes rows from a table. Adding a WHERE clause gives the database a condition to evaluate for each row. Rows that satisfy the condition are selected for deletion; rows that do not satisfy it remain in the table.

sql
Bohemian RhapsodyBohemian RhapsodyMy WayImagineImagineMy Waycondition falsecondition false
Which rows does DELETE remove when the WHERE condition matches only certain records?

Tracing a Matching Condition

Worked example

Removing tracks titled My Way

Consider a Track table containing Bohemian Rhapsody, My Way, Imagine, and a second My Way entry. What remains after applying DELETE with the condition title = 'My Way'?

  1. 1
    Check the first row

    For Bohemian Rhapsody, title = 'My Way' is false, so the row remains.

  2. 2
    Check the matching rows

    For each My Way entry, title = 'My Way' is true, so both matching rows are selected for deletion.

  3. 3
    Check the remaining nonmatching row

    For Imagine, the condition is false, so that row remains.

Result

After the statement executes, only Bohemian Rhapsody and Imagine remain in the table.

The condition is evaluated independently for each row. This means a single DELETE statement can remove more than one row when multiple rows satisfy the same WHERE condition.

DELETE statementWHERE title = 'My Way'Track tableMatching rowsMy WayRemoved rowsRemaining rowsBohemian Rhapsody, Imagineexaminescondition is truedeletescondition is false
How does the DELETE command identify and remove matching rows from a table?

The Empty WHERE Clause Trap

sql

When DELETE FROM Track is executed without a WHERE clause, there is no condition to evaluate. Every row matches the implicit meaning of the statement, so every row is deleted and the table becomes empty.

Bohemian RhapsodyEmpty Track tableMy WayImagineno conditionno conditionno condition
What happens to the table when DELETE is executed without a WHERE clause?

Checking Before Deleting

Before executing DELETE, test the WHERE condition with SELECT. This lets you verify which rows the condition identifies before you remove them. The check is especially important because the WHERE clause determines the complete set of rows affected by the deletion.

Test with SELECTVerify target rowsExecute DELETECall commit()Stop and reviseconditioninspect resultstargets are correcttargets are wrongfinalize change
What sequence of steps should be followed to check the target rows, delete them, and commit the change safely?
  1. 1
    Use SELECT to test the condition before deletion.
  2. 2
    Verify that the rows identified are exactly the rows intended for removal.
  3. 3
    Execute DELETE with the specific WHERE condition.
  4. 4
    Call commit() to finalize the pending deletion.

Finalizing the Transaction

In many database systems, DELETE does not immediately make the removal permanent. Instead, the rows are marked for deletion within a transaction. Calling commit() tells the database to finalize pending changes and write them to disk, making the deletion permanent and visible to other users or programs accessing the database.

sql
DELETEPending deletioncommit()Saved database changemarks rowsfinalizewrites change
How does the deletion move from a pending transaction to a permanently saved database change?

Common Deletion Mistakes

  • Common mistake

    Omitting the WHERE clause

    What it looks like
    DELETE FROM Track;
    Why it fails

    Without a condition, every row in Track is eligible for deletion, leaving the table empty.

    Do this instead

    Include a specific WHERE condition that identifies the intended rows.

  • Common mistake

    Skipping the SELECT check

    Why it fails

    You may not notice that the condition identifies more rows than intended until after DELETE executes.

    Do this instead

    Test the WHERE condition with SELECT and verify the target rows before deleting.

  • Common mistake

    Forgetting to call commit()

    Why it fails

    The deletion may remain a pending transaction rather than becoming a finalized database change.

    Do this instead

    Call commit() after executing DELETE unless the database library or framework explicitly handles committing automatically.

  • Common mistake

    Assuming a matching title identifies only one row

    What it looks like
    DELETE FROM Track WHERE title = 'My Way';
    Why it fails

    Every row whose title equals My Way matches the condition, so multiple rows can be deleted.

    Do this instead

    Use SELECT first to check how many rows satisfy the condition.

Practice the Safe Sequence

EASY

A Track table contains several songs. You intend to remove only the rows whose title is My Way. Describe the sequence you should follow before the deletion becomes a permanent database change.

Hints
  • Begin by checking the target rows with SELECT.
  • Use a WHERE condition that identifies the intended title.
  • After DELETE, call commit() to finalize the change.

What do you think happens?

What happens if DELETE FROM Track is executed without a WHERE clause?

  • Only the first row is deleted

  • No rows are deleted

  • Every row in Track is deleted

  • Only rows titled My Way are deleted

Reveal answer
Answer:

Every row in Track is deleted.

Without a WHERE clause, the database has no condition to evaluate, so every row matches the statement.

Key Takeaways

  1. 1
    DELETE removes rows from a table.
  2. 2
    WHERE filters the rows so that only matching records are selected for deletion.
  3. 3
    Leaving out WHERE causes every row in the table to be deleted.
  4. 4
    Use SELECT before DELETE to verify the rows targeted by the condition.
  5. 5
    Call commit() after DELETE to finalize the deletion and save the database change.

At a glance

  • A DELETE statement removes rows, while its WHERE clause determines which rows match.
  • The WHERE condition is evaluated for each row, so one DELETE can remove multiple matching rows.
  • A DELETE statement without WHERE deletes every row in the table.
  • Testing the condition with SELECT is a practical safeguard before deletion.
  • Calling commit() finalizes the pending deletion and makes the change permanent.

More concepts starting with Q