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.

---

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.

---

C. Performance

• DELETE is slower than TRUNCATE 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 the WHERE condition with a SELECT 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.

---