Concepts / Inserting Data with INSERT: Adding New Rows

Inserting Data with INSERT: Adding New Rows

DELETE removes rows from a table; the WHERE clause specifies which rows to delete.

  • Programming

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.

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.

DELETE matching rowsTrack tableBohemian Rhapsody, My Way,Imagine, My WayTrack tableBohemian Rhapsody, Imagine
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

Remove Tracks with a Specific Title

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

Choose the table: The deletion operates on the Track table.

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

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

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

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

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

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

matching rows removedevery row removedDELETE with WHEREtitle = 'My Way'Remaining rowsBohemian Rhapsody, ImagineDELETE withoutWHEREall rowsEmpty tableno rows
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.

  • Running DELETE without a WHERE clause.

    Without a condition, every row in Track is deleted.

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

  • Skipping the verification step.

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

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

  • Assuming DELETE is finalized without commit().

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

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

From Pending to Permanent

execute DELETEcall commit()Rows in tablematching rows presentPending deletiontransaction changeCommitted deletionchange finalized
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. Identify the table containing the rows to remove.
  2. Write the specific condition that should identify those rows.
  3. Test the condition with SELECT.
  4. Confirm that the selected rows are exactly the rows you intend to delete.
  5. Execute DELETE with the WHERE condition.
  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. DELETE removes rows from a table.
  2. WHERE limits the deletion to rows that satisfy a specific condition.
  3. Omitting WHERE deletes every row in the table.
  4. Testing the condition with SELECT helps verify the intended targets.
  5. Calling commit() finalizes the deletion.

Key Takeaways

  • 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

  • Updating Existing Data with UPDATE
  • Selecting Rows with SELECT
  • Adding New Rows with INSERT