Understanding WHERE Clauses: Filtering Rows
DELETE removes rows from a table; the WHERE clause specifies which rows to delete.
Why Deletion Needs Care
Deleting data is different from inserting or updating it. Inserting adds a row, and updating changes an existing value. Deletion removes rows, and recovering them typically requires restoring from a backup. Because deletion can be difficult to reverse, the condition that identifies the rows must be checked carefully.
A WHERE clause is the filter that tells DELETE which rows should be removed and which rows should remain untouched.
What do you think happens?
A Track table contains two rows whose title is My Way. What happens when the statement deletes rows whose title equals My Way?
Reveal answer
Answer: Both matching rows are deleted and other rows remain
The WHERE condition is evaluated for each row. Every row whose title equals My Way matches the condition and is removed; rows with different titles do not match.
How the Filter Selects Rows
The database evaluates the WHERE condition independently for each row. For a condition such as title = 'My Way', a row titled Bohemian Rhapsody does not match, so it remains. A row titled My Way matches, so it is selected for deletion. The same evaluation happens for every other row, including any second row with the title My Way.
The visual shows the essential behavior: matching rows are removed, while nonmatching rows survive. The condition does not mean “delete one row named My Way”; it identifies every row that satisfies the condition.
A Targeted DELETE
Remove Tracks with a Specific Title
Remove every row from Track whose title is My Way, while leaving tracks with other titles untouched.
Choose the table: Track is the table from which rows will be removed.
Add the filter: The condition title = 'My Way' identifies the rows to delete.
Evaluate each row: Rows with the title My Way match the condition. Bohemian Rhapsody and Imagine do not match.
Finalize the change: Call commit() after DELETE so the pending deletion is finalized and the change is permanently removed from the database.
The matching My Way rows are removed. Bohemian Rhapsody and Imagine remain.
The DELETE statement identifies the table and the WHERE clause identifies the rows. The separate commit() call finalizes the change. The exact way a program invokes these operations depends on its database library, but the safety pattern remains the same: execute the targeted DELETE, then commit the pending change.
The Empty-Condition Hazard
Omitting the WHERE clause.
Without a condition, the database has no row filter. Every row matches the implicit instruction to delete all rows, so the table becomes empty.
Fix:
Include a specific WHERE condition that identifies the intended rows.Skipping the verification step.
A condition can identify different rows from the ones you intended to remove.
Fix:
Test the WHERE clause with SELECT first, then execute DELETE after verifying the selected rows.Forgetting to call commit().
In many database systems, DELETE initially marks rows for deletion within a transaction rather than immediately finalizing the removal.
Fix:
Call commit() after DELETE unless the database library or framework handles committing automatically.
Checking Before Committing
- Write the intended WHERE condition.
- Test that condition with SELECT to verify which rows it identifies.
- Use the same condition in DELETE.
- Call commit() to finalize the deletion.
In many database systems, DELETE first marks matching rows for deletion within a transaction. Calling commit() tells the database to finalize pending changes, make the deletion visible to other users or programs accessing the database, and write the change to disk. Some libraries or frameworks commit automatically, but in many educational and professional contexts the program is responsible for calling commit() explicitly.
Apply the Pattern
A Track table contains rows with the titles Bohemian Rhapsody, My Way, Imagine, and a second My Way row. Describe which rows remain after executing DELETE FROM Track WHERE title = 'My Way', and explain why commit() is needed afterward.
Hints
- Evaluate the condition title = 'My Way' separately for each row.
- Rows with different titles do not match the condition.
- The deletion is finalized by calling commit().
Before running a DELETE statement, write the SELECT statement you would use to check which rows satisfy the same WHERE condition. Then explain what risk remains if the WHERE clause is omitted.
Hints
- Use the same table and condition in SELECT that you plan to use in DELETE.
- Without WHERE, every row in the table matches the deletion instruction.
Key Takeaways
- DELETE removes rows from a table.
- WHERE filters the rows that DELETE removes by evaluating a condition for each row.
- Omitting WHERE deletes every row in the table.
- Test the WHERE condition with SELECT before executing DELETE.
- Call commit() to finalize the pending deletion unless the database library handles commits automatically.
Key Takeaways
- DELETE removes rows, while WHERE determines which rows match the deletion.
- A condition is evaluated independently for each row, so all matching rows are removed and nonmatching rows remain.
- DELETE without WHERE removes every row in the table.
- Use SELECT to verify the target rows before DELETE.
- Call commit() to finalize the deletion in systems where the operation is transactional.