SQL DELETE Statement
The SQL DELETE statement is used to delete one or more records from a table in a database. It allows you to remove specific rows that meet certain conditions or delete all rows from a table.
The basic syntax of the DELETE statement is as follows:
|
1 2 |
DELETE FROM table_name WHERE condition; |
- table_name: The name of the table from which you want to delete records.
- condition: An optional clause that specifies which records to delete. If you omit the WHERE clause, all records in the table will be deleted.
Here’s an example:
|
1 2 |
DELETE FROM employees WHERE department = 'HR'; |
This SQL query deletes all records from the “employees” table where the department is ‘HR’.
If you want to delete all records from a table without any condition, you can use the following query:
|
1 |
DELETE FROM table_name; |
Be cautious when using the DELETE statement, especially without a WHERE clause, as it can result in the loss of all data in the specified table. Always make sure you have a backup or are certain about the records you are deleting.
Example 1: Delete a Specific Record
Suppose you have a “students” table, and you want to delete a student with a specific ID:
|
1 2 |
DELETE FROM students WHERE student_id = 101; |
This query deletes the record of the student with the ID 101 from the “students” table.
Example 2: Delete Records Based on a Condition
Consider an “orders” table where you want to delete all orders that were placed before a certain date:
|
1 2 |
DELETE FROM orders WHERE order_date < '2023-01-01'; |
This query removes all records from the “orders” table where the order date is before January 1, 2023.
Example 3: Delete All Records from a Table
If you want to delete all records from a table, you can omit the WHERE clause:
|
1 2 |
DELETE FROM customers; |
This query removes all records from the “customers” table. Be cautious when using this syntax, as it will delete all data from the specified table.
Example 4: Delete Duplicate Records
Suppose you have a “products” table with duplicate entries and you want to keep only one instance of each product:
|
1 2 3 4 |
DELETE p1 FROM products p1, products p2 WHERE p1.product_id > p2.product_id AND p1.product_name = p2.product_name; |
This query deletes duplicate records from the “products” table, keeping only the one with the lowest product_id.
Example 5: Delete Records Using a Subquery
You can use a subquery to identify records to delete. For example, let’s delete employees who have not placed any orders:
|
1 2 |
DELETE FROM employees WHERE employee_id NOT IN (SELECT DISTINCT employee_id FROM orders); |
This query deletes employees from the “employees” table if they do not have corresponding entries in the “orders” table.
Always exercise caution when using the DELETE statement, especially without a WHERE clause, to avoid unintentional data loss. It’s a good practice to test your DELETE statements with a SELECT statement first to see which records will be affected before executing the DELETE query.
