DELETE COMMAND
The DELETE
command in SQL is used to remove rows from a table based on specific conditions. It is part of the Data Manipulation Language (DML) and allows precise control over which rows to delete, unlike the TRUNCATE
command, which deletes all rows.
1. Purpose
The DELETE
command is used to:
- Remove specific rows matching a condition.
- Delete all rows from a table while retaining the table structure.
2. Syntax
A. Basic Syntax
DELETE FROM table_name
WHERE condition;
table_name
: Name of the table from which rows will be deleted.condition
: A filter specifying which rows to delete.
B. Delete All Rows (Caution)
If you want to delete all rows from a table:
DELETE FROM table_name;
⚠️ Warning: If no WHERE
clause is specified, all rows in the table will be deleted.
3. Examples
A. Delete Specific Rows
Delete an employee with emp_id = 101
:
DELETE FROM employees
WHERE emp_id = 101;
B. Delete Based on Multiple Conditions
Delete employees in department 20 earning less than 50,000:
DELETE FROM employees
WHERE department_id = 20 AND salary < 50000;
C. Delete All Rows
Remove all data from the employees
table:
DELETE FROM employees;
The table structure remains intact, and you can still insert new rows later.
D. Delete Using Subqueries
Delete rows based on another table's data:
DELETE FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location = 'New York'
);
E. Delete with Joins (Using Oracle-Specific Syntax)
Delete employees who belong to departments located in 'Chicago':
DELETE FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE e.department_id = d.department_id AND d.location = 'Chicago'
);
4. Key Points
A. Transactions
- The
DELETE
command is transactional:- Changes are not permanent until you issue a
COMMIT
. - You can undo changes with a
ROLLBACK
command.
- Changes are not permanent until you issue a
B. Constraints
- Rows that are referenced by foreign key constraints in other tables cannot be deleted unless:
- The foreign key is defined with
ON DELETE CASCADE
. - You delete the referencing rows first.
- The foreign key is defined with
C. Performance
DELETE
is slower thanTRUNCATE
because:- It logs each deleted row in the transaction log.
- It can trigger
DELETE
triggers and check constraints.
5. Comparison with TRUNCATE
Feature | DELETE | TRUNCATE |
---|---|---|
Deletes Specific Rows | Yes (with WHERE clause) |
No |
Deletes All Rows | Yes (without WHERE ) |
Yes |
Triggers Execution | Yes | No |
Transactional | Yes (can be rolled back) | No (cannot be rolled back) |
Resets Identity Column | No | Yes |
6. Practical Example
Suppose we have a table employees
:
emp_id | emp_name | department_id | salary |
---|---|---|---|
101 | Alice | 20 | 50000 |
102 | Bob | 30 | 60000 |
103 | Charlie | 20 | 55000 |
A. Delete a Single Row
DELETE FROM employees
WHERE emp_id = 101;
B. Delete Multiple Rows
Delete all employees in department 20:
DELETE FROM employees
WHERE department_id = 20;
C. Verify Changes
To check the remaining rows:
SELECT * FROM employees;
7. Common Errors
A. ORA-02292: Integrity constraint violated - child record found
Occurs when a row being deleted is referenced by a foreign key in another table. Solution:
- Delete the child rows first.
- Use
ON DELETE CASCADE
while defining the foreign key.
B. ORA-00933: SQL command not properly ended
Occurs if the DELETE
statement has incorrect syntax.
C. ORA-00942: Table or view does not exist
Occurs if the table name is misspelled or you lack the necessary privileges.
8. Best Practices
A. Use Transactions
- Always use transactions for critical operations:
DELETE FROM employees WHERE department_id = 30; COMMIT; -- To save changes ROLLBACK; -- To undo changes
B. Backup Important Data
- Take a backup of the table or database before deleting rows, especially for production data.
C. Test with a SELECT
First
- Before running a
DELETE
, test theWHERE
condition with aSELECT
query:SELECT * FROM employees WHERE department_id = 20;
9. Summary
Feature | Details |
---|---|
Delete Specific Rows | Use WHERE clause to specify conditions. |
Delete All Rows | Omit the WHERE clause (use cautiously). |
Transactions | Use COMMIT and ROLLBACK to manage changes. |
Constraints Handling | Ensure child records are deleted if necessary. |
Subquery Deletes | Use subqueries to delete based on related data. |
Comments
Post a Comment