Deleting Data with SQL

The DELETE statement in SQL is used to remove rows from a table. It is a powerful command that should be used with caution, as deleted data cannot be easily recovered without proper backups.

Basic DELETE Syntax

The simplest form of the DELETE statement removes all rows from a table:

DELETE FROM table_name;

Caution: This command will permanently remove all records from the specified table. Always ensure you have a backup before executing such a command.

Deleting Specific Rows

To delete specific rows, you use the WHERE clause to specify criteria for which rows to remove. This is the most common and safest way to use the DELETE statement.

DELETE FROM table_name
WHERE condition;

The condition specifies which rows will be deleted. If the condition evaluates to true for a row, that row will be deleted.

DELETE FROM Customers
WHERE CustomerID = 101;

DELETE FROM Customers
WHERE City = 'London';

DELETE FROM Customers
WHERE City = 'Paris' AND CustomerID < 50;

Using Subqueries in DELETE

You can also use subqueries within the WHERE clause to specify complex deletion criteria.

DELETE FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM InactiveCustomers);

Important Considerations

• Transactions: It is highly recommended to perform DELETE operations within a transaction. This allows you to roll back the changes if something goes wrong.
• Backups: Always maintain regular backups of your database.
• Permissions: Ensure the user executing the DELETE statement has the necessary privileges.
• TRUNCATE TABLE vs. DELETE:
• DELETE removes rows one by one and logs each deletion. It can be rolled back.
• TRUNCATE TABLE removes all rows from a table much faster than DELETE by deallocating the data pages. It cannot be easily rolled back and resets identity columns. Use TRUNCATE TABLE when you want to empty a table completely and don't need to log individual row deletions.