Querying Data with SELECT: Retrieving Information

Deletion Requires Deliberate Targeting

Deleting data is different from inserting or updating it. Inserting adds a row, and updating modifies an existing value. DELETE removes rows from a table, so the operation demands careful targeting. In most practical situations, recovering a deleted row requires restoring it from a backup.

Filtering Rows with WHERE

A DELETE statement removes rows from a table. Adding a WHERE clause gives the database a condition to evaluate for each row. Rows that satisfy the condition are selected for deletion; rows that do not satisfy it remain in the table.

DELETE FROM Track
WHERE title = 'My Way';

Tracing a Matching Condition

The condition is evaluated independently for each row. This means a single DELETE statement can remove more than one row when multiple rows satisfy the same WHERE condition.

The Empty WHERE Clause Trap

DELETE FROM Track;

When DELETE FROM Track is executed without a WHERE clause, there is no condition to evaluate. Every row matches the implicit meaning of the statement, so every row is deleted and the table becomes empty.

Checking Before Deleting

Before executing DELETE, test the WHERE condition with SELECT. This lets you verify which rows the condition identifies before you remove them. The check is especially important because the WHERE clause determines the complete set of rows affected by the deletion.

1. Use SELECT to test the condition before deletion.
2. Verify that the rows identified are exactly the rows intended for removal.
3. Execute DELETE with the specific WHERE condition.
4. Call commit() to finalize the pending deletion.

Finalizing the Transaction

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

DELETE FROM Track
WHERE title = 'My Way';

commit()

Common Deletion Mistakes

• Omitting the WHERE clause

  Without a condition, every row in Track is eligible for deletion, leaving the table empty.

  Fix: Include a specific WHERE condition that identifies the intended rows.

• Skipping the SELECT check

  You may not notice that the condition identifies more rows than intended until after DELETE executes.

  Fix: Test the WHERE condition with SELECT and verify the target rows before deleting.

• Forgetting to call commit()

  The deletion may remain a pending transaction rather than becoming a finalized database change.

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

• Assuming a matching title identifies only one row

  Every row whose title equals My Way matches the condition, so multiple rows can be deleted.

  Fix: Use SELECT first to check how many rows satisfy the condition.

Practice the Safe Sequence

Key Takeaways

1. DELETE removes rows from a table.
2. WHERE filters the rows so that only matching records are selected for deletion.
3. Leaving out WHERE causes every row in the table to be deleted.
4. Use SELECT before DELETE to verify the rows targeted by the condition.
5. Call commit() after DELETE to finalize the deletion and save the database change.