SQL - Delete Query

SQL - DELETE Statement

The SQL DELETE statement is used to remove existing records from a table.

Just like the UPDATE statement, you must use the WHERE clause to specify which rows to delete. If you omit the WHERE clause, all records in the table will be deleted!

Syntax

The basic syntax of the DELETE statement is:

DELETE FROM table_name
WHERE condition;

The WHERE clause identifies the specific row(s) to be removed.

Example

Using our Customers table:

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  6 | Komal    |  22 | Hyderabad   |  4500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
+----+----------+-----+-------------+----------+

The following query will delete the customer whose ID is 6.

DELETE FROM Customers
WHERE ID = 6;

After this operation, the row for ‘Komal’ is permanently removed from the table.

+----+----------+-----+-------------+----------+
| ID | Name     | Age | City        | Salary   |
+----+----------+-----+-------------+----------+
|  1 | Ramesh   |  32 | Ahmedabad   |  2000.00 |
|  2 | Khilan   |  25 | Delhi       |  1500.00 |
|  3 | Kaushik  |  23 | Kota        |  2000.00 |
|  4 | Chaitali |  25 | Mumbai      |  6500.00 |
|  5 | Hardik   |  27 | Bhopal      |  8500.00 |
|  7 | Muffy    |  24 | Indore      | 10000.00 |
+----+----------+-----+-------------+----------+

Deleting All Records

To delete all records from a table, you can omit the WHERE clause. However, this operation can be slow and resource-intensive for large tables.

-- WARNING: This deletes ALL rows from the table.
DELETE FROM Customers;

For deleting all records, the TRUNCATE TABLE command is almost always a better choice. It is much faster, uses fewer system resources, and resets any identity columns. See the TRUNCATE TABLE chapter for a detailed comparison.

Best Practice: Safety First

DELETE is a destructive operation. Before running a DELETE query, especially in a production environment:

1. Always have a backup.
2. Use a SELECT statement with the exact same WHERE clause to confirm which rows will be deleted. This is a crucial verification step.
3. Use transactions. Wrap your DELETE in a BEGIN TRANSACTION block. This allows you to ROLLBACK the deletion if you made a mistake.

-- Step 1: Verify what you are about to delete
SELECT * FROM Customers WHERE Age > 30;

-- Step 2: Perform the delete within a transaction
BEGIN TRANSACTION;

DELETE FROM Customers WHERE Age > 30;

-- You can perform other checks here.
-- If everything is correct, commit.
-- COMMIT;
-- If there's a problem, undo.
-- ROLLBACK;