SQL DELETE Statement
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
SQL DELETE Statement
The DELETE statement is used to remove one or more rows from a table based on a specified condition. It’s a powerful command that should be used with caution, as it permanently removes data from the table.
Basic Syntax
Syntax:
DELETE FROM table_name
WHERE condition;
Note: Always use the WHERE clause to specify which records to delete. Omitting it will remove all records from the table.
Example 1: Delete a Single Record
Delete a specific employee from the employees table based on their employee_id.
Example:
Remove the employee with employee_id 5:
DELETE FROM employees
WHERE employee_id = 5;
Output:
If the employee with employee_id 5 was in the table, they will be removed.
employee_id | first_name | last_name | department | salary | age |
5 | Anil | Kapoor | Sales | 49500 | 29 |
After deletion:
employee_id | first_name | last_name | department | salary | age |
6 | Neha | Sharma | Marketing | 47000 | 27 |
Example 2: Delete Multiple Records
Delete all employees who are in the ‘Marketing’ department.
Example:
Remove employees who work in ‘Marketing’:
DELETE FROM employees
WHERE department = ‘Marketing’;
Output:
All employees in the ‘Marketing’ department will be removed from the table.
employee_id | first_name | last_name | department | salary | age |
6 | Neha | Sharma | Marketing | 47000 | 27 |
7 | Sita | Rao | Finance | 55000 | 29 |
After deletion:
employee_id | first_name | last_name | department | salary | age |
7 | Sita | Rao | Finance | 55000 | 29 |
Example 3: Delete All Records
To delete all records from a table but keep the table structure intact:
Example:
DELETE FROM employees;
Output:
All records in the employees table will be removed, but the table structure remains.
employee_id | first_name | last_name | department | salary | age |
Tips to Remember
- Use WHERE Clause Carefully: Always include the WHERE clause to avoid deleting all rows in the table.
- Backup Data: Consider backing up your data before performing a delete operation, especially when deleting multiple rows.
- Test with SELECT: Before executing a DELETE statement, run a SELECT query with the same WHERE condition to see which rows will be affected.
- Transactions: Use transactions (BEGIN, COMMIT, ROLLBACK) to ensure you can undo changes if necessary.
Power of DELETE Statement
The DELETE statement:
- Removes Data: Permanently deletes one or more rows from a table.
- Supports Conditions: Allows you to specify conditions to delete targeted records.
- Maintains Structure: Retains the table structure while removing data.
SQL Syntax Best Practices
- Use Uppercase for SQL Keywords: Although SQL is not case-sensitive, it’s a common practice to write SQL keywords in uppercase for readability.
- Indentation and Line Breaks: Use indentation and line breaks to organize your code and make it more readable, especially for complex queries.
- Consistent Naming Conventions: Use consistent naming conventions for tables, columns, and other database objects. For example, snake_case or camelCase.
- Comment Your Code: Use comments to explain complex SQL queries. This is especially useful for queries that will be revisited or maintained by others.
— This query selects all employees in the Sales department
SELECT first_name, last_name
FROM employees
WHERE department = ‘Sales’;
FAQ
Q1: What if I accidentally delete the wrong rows?
A1: If using transactions, you can use ROLLBACK to undo changes. Otherwise, you may need to restore from a backup if available.
Q2: Can I use the DELETE statement to delete records based on values in another table?
A2: Yes, you can use a subquery to delete records based on values from another table.
Example:
DELETE FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE name = ‘Sales’);
Q3: How do I delete records that match complex conditions?
A3: Use logical operators (AND, OR) and parentheses in the WHERE clause to specify complex conditions.
Example:
DELETE FROM employees
WHERE department = ‘Sales’ AND age < 30;
Q4: Can I delete data from multiple tables at once?
A4: SQL does not support direct deletion from multiple tables in a single DELETE statement. You need to execute separate DELETE statements for each table.
Q5: How can I ensure that only the intended rows are deleted?
A5: Carefully construct the WHERE clause and verify which rows will be affected by running a SELECT query first.
