SQL DELETE Statement

Last Updated: October 26, 2023 | Documentation Version: 1.0

The DELETE statement in SQL is used to remove one or more records from a table.

Syntax

DELETE FROM table_name WHERE condition;

Description

The DELETE statement removes rows from a table. You can specify which rows to delete using a WHERE clause. If you omit the WHERE clause, all records in the table will be deleted.

Basic Syntax Components:

DELETE FROM table_name: Specifies the table from which to remove rows.
WHERE condition: An optional clause that specifies the criteria for selecting rows to delete. If this clause is omitted, all rows in the table will be deleted.

Examples

1. Deleting Specific Rows

This example deletes records from the Customers table where the CustomerID is 10.

DELETE FROM Customers
WHERE CustomerID = 10;

2. Deleting Multiple Rows Based on a Condition

This example deletes all customers from the Customers table who are located in 'London'.

DELETE FROM Customers
WHERE City = 'London';

3. Deleting All Rows (Caution!)

This statement will remove all data from the Orders table. Use with extreme caution!

DELETE FROM Orders;

Important Considerations

Warning: Deleting Data

Deleting data is a permanent operation. Once a row is deleted, it cannot be recovered unless you have a backup or transaction log. Always ensure you understand the implications of your DELETE statement, especially when running it in a production environment. It is highly recommended to use a SELECT statement with the same WHERE clause first to verify which rows will be affected before executing the DELETE statement.

Using Joins with DELETE (Specific to certain SQL dialects like SQL Server, MySQL)

In some SQL databases, you can join tables to delete records based on conditions involving multiple tables.

Example (MySQL syntax): Deleting customers who have placed an order.

DELETE c
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate < '2023-01-01';

Note: The exact syntax for deleting with joins can vary between database systems (e.g., SQL Server, PostgreSQL, MySQL). Consult your specific database documentation for details.

Transaction Management

It's good practice to wrap DELETE operations within a transaction. This allows you to rollback the changes if something goes wrong or if you decide to undo the operation before committing.

START TRANSACTION; -- or BEGIN TRANSACTION;

DELETE FROM Products
WHERE UnitsInStock = 0;

-- Verify the deletion or perform other operations

-- To commit the changes:
-- COMMIT;

-- To undo the changes:
-- ROLLBACK;