Updating Data with UPDATE: Modifying Existing Rows
DELETE removes rows from a table; the WHERE clause specifies which rows to delete.
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.
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.
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
Removing matching tracks
Remove every row from the Track table whose title equals 'My Way'.
Identify the table: The operation applies to the Track table.
Specify the filter: The WHERE condition title = 'My Way' tells the database which rows qualify.
Evaluate each row: Each row is checked independently. Matching rows are selected for deletion; nonmatching rows are left untouched.
Finalize the change: After executing DELETE, call commit() to finalize the pending deletion.
The rows titled My Way are deleted. Bohemian Rhapsody and Imagine remain in the Track table after the change is committed.
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.
Executing DELETE without a WHERE clause
With no condition to evaluate, every row matches and the entire table 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 more rows than intended if it has not been checked.
Fix:
Use SELECT with the same WHERE condition first, then execute DELETE only after confirming the matching rows.Assuming deletion is equivalent to updating a value
Updating modifies an existing value, while deletion removes the row and is irreversible in most practical situations.
Fix:
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.
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
- Identify the table containing the rows to remove.
- Write a specific WHERE condition that describes the intended rows.
- Test that condition with SELECT and inspect the matching rows.
- Execute DELETE using the verified condition.
- Call commit() to finalize the deletion.
Check Your Reasoning
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?
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
- DELETE removes rows from a table; UPDATE changes values in existing rows.
- A WHERE clause filters the rows that DELETE can remove.
- Omitting WHERE causes every row in the table to be deleted.
- Test the WHERE condition with SELECT before executing DELETE.
- Call commit() to finalize the deletion and make the change permanent.
Key Takeaways
- 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.