SQL DELETE Statement

The SQL DELETE statement is an essential command in SQL used to remove one or more rows from a database table. Unlike the DROP statement, which removes the entire table, the DELETE statement removes data (rows) from the table retaining only the table structure, constraints, and schema. Whether you need to delete a single row based on a condition or remove an entire dataset, understanding this command will help you manage your data effectively.

Using SQL DELETE Statement for Data Removal

The SQL DELETE statement is part of the Data Manipulation Language (DML) and is used to remove data from a table. The key advantage of using DELETE is that it allows you to specify a condition (using the WHERE clause) to delete only the rows that match certain criteria. This ensures you don’t accidentally remove all the data from the table unless that is your intention.

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', '[email protected]', 'Development'),
(2, 'Praveen', '[email protected]', 'HR'),
(3, 'Bisa', '[email protected]', 'Sales'),
(4, 'Rithvik', '[email protected]', 'IT'),
(5, 'Suraj', '[email protected]', 'Quality Assurance'),
(6, 'Om', '[email protected]', 'IT'),
(7, 'Naruto', '[email protected]', '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.

Best Practices for Using SQL DELETE

To use the SQL DELETE statement safely and efficiently, here are some best practices:

1. Use Transactions: Wrap DELETE statements in transactions to provide an option to roll back changes if necessary. This ensures data integrity.
2. Always Use a WHERE Clause: Avoid deleting all rows by accident. Always filter records using a WHERE clause to specify which rows to delete. Omitting the WHERE clause will delete all records.
3. Backup Data: Before performing large deletions, ensure that you have a backup of the data to avoid irreversible loss.
4. Test on Development Server: Always test your DELETE queries on a development or staging environment to ensure they produce the desired result before executing them on a live database.
5. Optimize Deletions: For large datasets, delete records in batches to reduce performance impact and avoid long-running queries.
6. Use Caution When Deleting Data: Be extra cautious when deleting records from sensitive or production databases. Always verify the condition in the WHERE clause to ensure you're deleting the right data.

Conclusion

The SQL DELETE statement is a powerful tool for removing data from a database table based on specific conditions. By understanding the correct syntax, how to delete individual or multiple records, and how to handle performance issues, you can use the DELETE statement efficiently and safely. Always be careful when deleting data, especially when working with large tables or sensitive information. By following best practices and using habits, you can ensure that your data remains authentic and secure.