The DELETE statement in SQL is used to delete existing records from a table. This statement is used to remove one or more rows from a database table based on certain conditions. Once the data is deleted, it cannot be recovered.
Syntax
The syntax of the DELETE statement is as follows:
DELETE FROM table_name WHERE condition;
Here, “table_name” represents the name of the table from which the data needs to be deleted, and “condition” represents the conditions to be met to delete the data. The WHERE clause is optional and it is used to filter the rows that need to be deleted.
Examples
Consider a table named “employees” with the following data:
| ID | Name | Age | City |
|---|---|---|---|
| 1 | John | 30 | New York |
| 2 | Jane | 25 | London |
| 3 | Jim | 35 | Paris |
Delete All Rows
To delete all rows from the “employees” table, the following query can be used:
DELETE FROM employees;
This query will delete all rows from the “employees” table and the result will be an empty table.
Delete Rows Based on a Condition
To delete only the rows that meet a specific condition, the WHERE clause can be used. For example, to delete the rows of employees who are 30 years old, the following query can be used:
DELETE FROM employees WHERE Age = 30;
This query will delete the row with ID “1” and the result will be:
| ID | Name | Age | City |
|---|---|---|---|
| 2 | Jane | 25 | London |
| 3 | Jim | 35 | Paris |
Notes
- It is important to use the DELETE statement with caution as it can result in data loss, which cannot be undone.
- The WHERE clause is optional but recommended as it helps to filter the rows that need to be deleted. Without the WHERE clause, all rows in the table will be deleted.
- In some cases, it is possible to use the TRUNCATE statement instead of the DELETE statement to delete all rows from a table. TRUNCATE is faster and more efficient than DELETE in terms of performance, but it cannot be used if you want to delete only specific rows based on a condition.
In conclusion, the DELETE statement is an important and commonly used statement in SQL that is used to delete existing records from a database table. With the use of the WHERE clause, it allows you to delete only specific rows based on certain conditions, giving you more control over the data in your table. As always, be careful and mindful when using the DELETE statement to avoid data loss.
