Concepts / Inserting Data with INSERT: Adding New Rows

Inserting Data with INSERT: Adding New Rows

The WHERE clause is the safety mechanism of DELETE: it tells the database which rows should be removed and which rows should remain.

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

A Deletion Needs a Target

Deleting data is not the same as inserting or updating it. Inserting adds a new row, and updating modifies an existing value. DELETE removes rows, and recovering deleted data typically requires restoring it from a backup. That is why a deletion must identify its target carefully.

Key point

The WHERE clause is the safety mechanism of DELETE: it tells the database which rows should be removed and which rows should remain.

How WHERE Selects Rows

Imagine a Track table containing several songs. The WHERE clause examines each row independently. A row whose title matches the condition is selected for deletion; a row that does not match remains in the table.

Track tableBohemian Rhapsody, My Way,Imagine, My WayTrack tableBohemian Rhapsody, ImagineDELETE matching rows
Which rows are removed when the condition title = 'My Way' is applied?

For the condition title = 'My Way', the two rows titled 'My Way' match and are deleted. 'Bohemian Rhapsody' and 'Imagine' do not match, so they remain.

A Worked Track Deletion

Worked example

Remove Tracks with a Specific Title

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

  1. 1
    Choose the table

    The deletion operates on the Track table.

  2. 2
    Choose the condition

    Use the condition title = 'My Way' so the database selects only rows whose title equals 'My Way'.

  3. 3
    Execute DELETE

    The DELETE statement removes the matching rows. Rows with other titles are not selected.

  4. 4
    Check the result

    Before executing DELETE, test the same WHERE condition with SELECT. This verifies which rows the condition will target.

  5. 5
    Finalize the change

    Call commit() after DELETE to finalize the deletion and ensure the change is permanently removed from the database.

Result

The rows titled 'My Way' are removed. The rows titled 'Bohemian Rhapsody' and 'Imagine' remain.

Best practice

Treat the sequence as two deliberate checks: first test the WHERE condition with SELECT, then execute DELETE with that same condition, and finally call commit().

The Risk of Missing WHERE

DELETE with WHEREtitle = 'My Way'Remaining rowsBohemian Rhapsody, ImagineDELETE withoutWHEREall rowsEmpty tableno rowsmatching rows removedevery row removed
What remains when DELETE includes a WHERE clause, and what happens when the clause is omitted?

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

  • Common mistake

    Running DELETE without a WHERE clause.

    What it looks like

    DELETE FROM Track

    Why it fails

    Without a condition, every row in Track 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.

    Why it fails

    A condition can target different rows from the ones you intended to remove.

    Do this instead

    Run SELECT with the intended WHERE condition first so you can verify the target rows.

  • Common mistake

    Assuming DELETE is finalized without commit().

    Why it fails

    In many database systems, DELETE marks rows for deletion within a transaction instead of immediately finalizing the change.

    Do this instead

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

From Pending to Permanent

Rows in tablematching rows presentPending deletiontransaction changeCommitted deletionchange finalizedexecute DELETEcall commit()
What changes when DELETE is executed, and how does commit() finalize that change?

In many database systems, executing DELETE creates a pending transaction change. Calling commit() tells the database to finalize the change and write it to disk, making the deletion permanent and visible to other users or programs accessing the database.

Deletion Checklist

  1. 1
    Identify the table containing the rows to remove.
  2. 2
    Write the specific condition that should identify those rows.
  3. 3
    Test the condition with SELECT.
  4. 4
    Confirm that the selected rows are exactly the rows you intend to delete.
  5. 5
    Execute DELETE with the WHERE condition.
  6. 6
    Call commit() to finalize the deletion.
EASY

A Track table contains rows titled 'Bohemian Rhapsody', 'My Way', 'Imagine', and another 'My Way'. Describe which rows should remain after deleting rows whose title equals 'My Way', and state what you should do before executing DELETE.

Hints
  • Evaluate the WHERE condition separately for each row.
  • Use SELECT with the intended condition before DELETE.
  • Remember to call commit() after the deletion.

Safe Deletion Summary

  1. 1
    DELETE removes rows from a table.
  2. 2
    WHERE limits the deletion to rows that satisfy a specific condition.
  3. 3
    Omitting WHERE deletes every row in the table.
  4. 4
    Testing the condition with SELECT helps verify the intended targets.
  5. 5
    Calling commit() finalizes the deletion.

At a glance

  • Use DELETE to remove rows, and use WHERE to identify exactly which rows should be removed.
  • Always test the WHERE condition with SELECT before executing DELETE.
  • A DELETE statement without WHERE removes every row in the table.
  • Call commit() after DELETE to finalize the change.

Related Topics

Related concepts

More concepts starting with I