SQL DELETE Statement

The SQL DELETE statement is used to remove specific rows from a table while keeping the table structure intact. It is different from DROP, which deletes the entire table.

• Removes rows based on conditions.
• Retains table schema, constraints, and indexes.
• Can delete a single row or all rows.
• Useful for cleaning or managing datasets.

Syntax: 

DELETE FROM table_name
WHERE some_condition;

Parameter Explanation

• Some_condition: A condition used to filter the rows you want to delete.
• table_name: The name of the table from which you want to delete the rows

Note: We can delete single as well as multiple records depending on the condition we provide in the WHERE clause. If we omit the WHERE clause then all of the records will be deleted and the table will be empty.

Examples of SQL DELETE Statement

Assume we have created a table named GFG_Employee in SQL, which contains the personal details of the Employee including their id, name, email and department etc. as shown below.

CREATE TABLE GFG_Employees (
id INT PRIMARY KEY,
name VARCHAR (20) ,
email VARCHAR (25),
department VARCHAR(20)
);

INSERT INTO GFG_Employees (id, name, email, department) VALUES
(1, 'Jessie', 'jessie23@gmail.com', 'Development'),
(2, 'Praveen', 'praveen_dagger@yahoo.com', 'HR'),
(3, 'Bisa', 'dragonBall@gmail.com', 'Sales'),
(4, 'Rithvik', 'msvv@hotmail.com', 'IT'),
(5, 'Suraj', 'srjsunny@gmail.com', 'Quality Assurance'),
(6, 'Om', 'OmShukla@yahoo.com', 'IT'),
(7, 'Naruto', 'uzumaki@konoha.com', 'Development');

Select * From GFG_Employees

Output

Example 1: Deleting Single Record

We can use the DELETE statement with a condition to delete a specific row from a table. The WHERE clause ensures only the intended record is removed. We can delete the records named Rithvik by using the below query:

Query:

DELETE FROM GFG_Employees WHERE NAME = 'Rithvik';

Output:

Example 2: Deleting Multiple Records

To delete multiple records, you can specify a condition that matches several rows. Let's delete the rows from the table  GFG_Employees where the department is "Development". This will delete 2 rows (the first row and the seventh row).

Query:

DELETE FROM GFG_Employees WHERE department = 'Development';

Output

Example 3: Delete All Records from a Table

If we need to delete all records from the table, we can omit the WHERE clause, or alternatively use the DELETE statement with an asterisk (*) to denote all rows.

Query:

DELETE FROM GFG_Employees; Or DELETE * FROM GFG_Employees;

Output:

All of the records in the table will be deleted, there are no records left to display. The table GFG_Employees  will become empty.

Rolling Back DELETE Operations

Since the DELETE statement is a DML operation, it can be rolled back when executed in a statement. If you accidentally delete records or need to repeat the process, you can use the ROLLBACK command.

Query:

START TRANSACTION; DELETE FROM GFG_Employees WHERE department = 'Development'; -- If needed, you can rollback the deletion ROLLBACK;

Explanation: The ROLLBACK command will undo the changes made by the DELETE statement, effectively restoring the records that were deleted during the transaction.