Concepts / Updating Data with UPDATE: Modifying Existing Rows

Updating Data with UPDATE: Modifying Existing Rows

A DELETE statement removes rows, while its WHERE clause identifies which rows are eligible for removal.

  • 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 Precision

The source topic names UPDATE, but the operation covered here is DELETE. UPDATE changes a value in an existing row. DELETE removes rows from a table entirely. Because deletion is irreversible in most practical situations, the condition that identifies the rows to remove must be checked carefully.

Key point

A DELETE statement removes rows, while its WHERE clause identifies which rows are eligible for removal.

How WHERE Selects Rows

The WHERE clause acts as a gatekeeper. The database evaluates its condition for each row independently. Rows that satisfy the condition are selected for deletion. Rows that do not satisfy it remain in the table.

Bohemian Rhapsodytitle condition: falseBohemian RhapsodyremainsMy Waytitle condition: trueImagineremainsImaginetitle condition: falseMy Waytitle condition: truecondition is falsecondition is false
Which rows match the condition title = 'My Way', and which rows remain after DELETE executes?

In the Track example, the condition is title = 'My Way'. The two rows titled My Way match the condition and are removed. Bohemian Rhapsody and Imagine do not match, so they remain.

A Targeted DELETE Operation

Worked example

Removing matching tracks

Remove every row from the Track table whose title equals 'My Way'.

  1. 1
    Identify the table

    The operation applies to the Track table.

  2. 2
    Specify the filter

    The WHERE condition title = 'My Way' tells the database which rows qualify.

  3. 3
    Evaluate each row

    Each row is checked independently. Matching rows are selected for deletion; nonmatching rows are left untouched.

  4. 4
    Finalize the change

    After executing DELETE, call commit() to finalize the pending deletion.

Result

The rows titled My Way are deleted. Bohemian Rhapsody and Imagine remain in the Track table after the change is committed.

Best practice

Before executing DELETE, test the same WHERE condition with SELECT. This verifies that the condition identifies exactly the rows you intend to remove.

The Empty-Condition Trap

A DELETE statement without a WHERE clause has no condition to evaluate. Every row matches the implicit condition of all rows, so the entire table is deleted and becomes empty.

Bohemian Rhapsodyrow in TrackEmpty Track tableevery row removedMy Wayrow in TrackImaginerow in Trackno WHERE conditionno WHERE conditionno WHERE condition
What happens when DELETE is executed without a WHERE clause?
  • Common mistake

    Executing DELETE without a WHERE clause

    What it looks like

    DELETE FROM Track

    Why it fails

    With no condition to evaluate, every row matches and the entire table is deleted.

    Do this instead

    Include a specific WHERE condition and test that condition with SELECT before executing DELETE.

  • Common mistake

    Skipping the verification step

    What it looks like

    Running DELETE immediately after writing a condition

    Why it fails

    A condition can target more rows than intended if it has not been checked.

    Do this instead

    Use SELECT with the same WHERE condition first, then execute DELETE only after confirming the matching rows.

  • Common mistake

    Assuming deletion is equivalent to updating a value

    What it looks like

    Treating removal of a row as an ordinary data edit

    Why it fails

    Updating modifies an existing value, while deletion removes the row and is irreversible in most practical situations.

    Do this instead

    Review the target condition carefully and recognize that recovery typically requires a backup.

Committing the Transaction

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

Matching rowstored in tablePending deletiontransaction not finalizedRow removedchange committedDELETEcommit()
How does a deleted row move from a pending transaction state to permanently removed data after commit()?
Key point

The practical sequence is: execute DELETE with a carefully checked WHERE clause, then immediately call commit(). Some libraries or frameworks commit automatically, but in most educational and professional contexts you are responsible for calling commit() explicitly.

Safe Deletion Checklist

  1. 1
    Identify the table containing the rows to remove.
  2. 2
    Write a specific WHERE condition that describes the intended rows.
  3. 3
    Test that condition with SELECT and inspect the matching rows.
  4. 4
    Execute DELETE using the verified condition.
  5. 5
    Call commit() to finalize the deletion.

Check Your Reasoning

MEDIUM

A Track table contains Bohemian Rhapsody, two rows titled My Way, and Imagine. You want to remove only the rows titled My Way. Describe what you should verify before DELETE and what operation finalizes the change.

Hints
  • The verification should use SELECT with the same condition intended for DELETE.
  • The condition should match both My Way rows and not the other tracks.
  • The finalization operation is commit().

What do you think happens?

What happens if DELETE is executed on the Track table without a WHERE clause?

  • Only the first row is removed

  • Only rows with empty titles are removed

  • Every row is deleted

  • The database automatically adds a safe condition

Reveal answer
Answer:

Every row is deleted.

Without a WHERE clause, the database has no condition to evaluate, so every row matches and the table becomes empty.

Key Takeaways

  1. 1
    DELETE removes rows from a table; UPDATE changes values in existing rows.
  2. 2
    A WHERE clause filters the rows that DELETE can remove.
  3. 3
    Omitting WHERE causes every row in the table to be deleted.
  4. 4
    Test the WHERE condition with SELECT before executing DELETE.
  5. 5
    Call commit() to finalize the deletion and make the change permanent.

At a glance

  • DELETE removes complete rows, unlike UPDATE, which modifies existing values.
  • WHERE is the safety filter that identifies which rows should be deleted.
  • A DELETE without WHERE removes every row in the table.
  • Use SELECT to verify the target rows before deletion.
  • Use commit() to finalize the deletion.

Related Topics

More concepts starting with U