sql_delete

SQL DELETE Statement

The DELETE statement in SQL is used to remove existing records (rows) from a table. It’s a powerful command, so it must be used with caution.

The SQL DELETE Statement Explained

The DELETE statement removes one or more rows from a table based on a specified condition. If no condition is provided, all rows in the table will be deleted.

SQL DELETE Syntax

DELETE FROM table_name
WHERE condition;

table_name: The name of the table from which to delete rows.WHERE condition: An optional clause that specifies which rows to delete. If omitted, all rows are deleted.

Demo Database

Let’s use a conceptual “Customers” table for our examples.

Initial “Customers” table data:

CustomerID | CustomerName                        | ContactName     | City        | Country
---------- | ----------------------------------- | --------------- | ----------- | -------
1          | Alfreds Futterkiste               | Maria Anders    | Berlin      | Germany
2          | Ana Trujillo Emparedados y helados  | Ana Trujillo    | México D.F. | Mexico
3          | Antonio Moreno Taquería             | Antonio Moreno  | México D.F. | Mexico
4          | Around the Horn                     | Thomas Hardy    | London      | UK
5          | Berglunds snabbköp                | Christina Berglund | Luleå       | Sweden

SQL DELETE Example

Suppose we want to delete the customer “Alfreds Futterkiste” from the “Customers” table. It’s best practice to use a unique identifier like CustomerID if available.

Using CustomerID (recommended):

DELETE FROM Customers
WHERE CustomerID = 1;

Using CustomerName (if CustomerID is unknown, but be cautious of duplicates):

DELETE FROM Customers
WHERE CustomerName = 'Alfreds Futterkiste';

After executing one of these statements, the “Customers” table will look like this (assuming CustomerID 1 was ‘Alfreds Futterkiste’):

CustomerID | CustomerName                        | ContactName     | City        | Country
---------- | ----------------------------------- | --------------- | ----------- | -------
2          | Ana Trujillo Emparedados y helados  | Ana Trujillo    | México D.F. | Mexico
3          | Antonio Moreno Taquería             | Antonio Moreno  | México D.F. | Mexico
4          | Around the Horn                     | Thomas Hardy    | London      | UK
5          | Berglunds snabbköp                | Christina Berglund | Luleå       | Sweden

Deleting All Rows from a Table

To delete all rows in a table without deleting the table itself (i.e., the table structure, columns, indexes, and constraints remain), you can use DELETE without a WHERE clause:

DELETE FROM table_name;

Extreme caution is advised with this command.

Alternative: TRUNCATE TABLE

Many database systems offer the TRUNCATE TABLE command as a faster way to delete all rows from a table.

TRUNCATE TABLE table_name;

Key differences between DELETE FROM table_name; and TRUNCATE TABLE table_name;:

• Speed: TRUNCATE is generally much faster as it deallocates data pages directly and logs less information.
• Triggers: DELETE fires row-level DELETE triggers. TRUNCATE usually does not (behavior can vary by RDBMS).
• Transaction Log: DELETE logs each row deletion, which can fill up the transaction log. TRUNCATE logs the deallocation of pages, using fewer resources.
• Auto-increment: TRUNCATE typically resets auto-incrementing identity columns back to their seed value. DELETE does not.
• Rollback: DELETE is a DML command and can be rolled back within a transaction. TRUNCATE is often considered a DDL command (or DML with DDL-like characteristics) and its rollback behavior can vary; some RDBMS don’t allow rollback or handle it differently.
• WHERE clause: TRUNCATE cannot have a WHERE clause; it always removes all rows.

Choose TRUNCATE when you need to quickly empty a table and the side effects (like not firing triggers or resetting identity) are acceptable or desired. For conditional deletion or when row-level actions are needed, use DELETE.

Best Practices:

• Always use a WHERE clause unless you intend to delete all rows.
• Prefer using primary keys or unique identifiers in your WHERE clause for precise deletion.
• Test your WHERE condition with a SELECT statement first to ensure you are targeting the correct rows.
• Ensure you have recent database backups before performing large-scale delete operations.
• Consider using transactions if you need to perform multiple related DELETE operations as an atomic unit.