MSDN Documentation

Deleting Data with the DELETE Statement in SQL

The DELETE statement is used to remove one or more records from a table. It's a fundamental operation for managing data in your SQL database.

Basic Syntax

The simplest form of the DELETE statement allows you to remove all rows from a table. Use this with extreme caution as it permanently removes all data.

DELETE FROM TableName;

To remove specific rows, you must use a WHERE clause. This clause specifies the condition(s) that determine which rows will be deleted.

DELETE FROM TableName
WHERE Condition;

Example Scenarios

1. Deleting a Specific Record

Let's say you have a Customers table and you want to delete the customer with CustomerID 101.

DELETE FROM Customers
WHERE CustomerID = '101';

2. Deleting Multiple Records Based on a Condition

To delete all customers who are located in 'London'.

DELETE FROM Customers
WHERE City = 'London';

3. Deleting Records Based on a Range

Delete all orders placed before January 1st, 2023.

DELETE FROM Orders
WHERE OrderDate < '2023-01-01';

Important Considerations

• Data Loss: Once data is deleted using the DELETE statement, it cannot be easily recovered without backups. Always ensure you have a backup or are absolutely certain about the rows you are deleting.
• WHERE Clause: Never omit the WHERE clause when deleting from a production database unless you intend to delete all records.
• Transactions: Use transactions (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION) to ensure atomicity and the ability to undo unintended deletions.
• Permissions: You need appropriate permissions (e.g., DELETE permission) on the table to execute this statement.

SELECT * FROM TableName WHERE Condition;

Deleting from Multiple Tables (JOIN DELETE)

Some SQL dialects support deleting records from one table based on conditions in another table using a join. The exact syntax can vary.

Example (SQL Server syntax):

DELETE t1
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.CommonColumn = t2.CommonColumn
WHERE t2.SomeCondition = 'Value';

Always consult your specific database system's documentation for the most accurate syntax for JOIN DELETE operations.