SQL DELETE Statement

 The DELETE statement in SQL is used to remove records from a table based on a specified condition. It is important to use it carefully because once records are deleted, they cannot be easily recovered (unless you have a backup or transaction logs).

Basic Syntax:

DELETE FROM table_name
WHERE condition;

• table_name: The name of the table from which you want to delete records.
• condition: A condition to specify which rows should be deleted. If you omit the WHERE clause, all rows in the table will be deleted.

Examples:

1. Delete a Single Record: To delete a specific row where the id is 10:

   DELETE FROM employees
   WHERE id = 10;
   

2. Delete Multiple Records: To delete all employees in the employees table who are from the department with department_id = 5:

   DELETE FROM employees
   WHERE department_id = 5;
   

3. Delete All Records (Be Careful!): To delete all rows from a table, simply omit the WHERE clause:

   DELETE FROM employees;
   

   Warning: This will remove all records from the employees table but will not delete the table itself.

4. Delete Using a Subquery: You can also delete records based on a subquery. For example, to delete employees who earn less than the average salary:

   DELETE FROM employees
   WHERE salary < (SELECT AVG(salary) FROM employees);
   

5. Delete Records with JOIN (Using Subquery in WHERE Clause): If you want to delete records in one table based on data from another table, you can use a JOIN. Example: Delete orders placed by customers who have not placed any orders in the last year.

   DELETE o
   FROM orders o
   JOIN customers c ON o.customer_id = c.customer_id
   WHERE c.last_order_date < '2023-01-01';
   

Important Notes:

• Transactions: If you're deleting critical data, you may want to use transactions to ensure data integrity. For example:

  BEGIN TRANSACTION;
  DELETE FROM employees WHERE department_id = 5;
  COMMIT;
  

  If something goes wrong, you can use ROLLBACK to undo the changes.

• Foreign Key Constraints: If there are foreign key constraints in place, you might not be able to delete rows that are being referenced by other tables unless you either remove those references first or update the foreign key constraint to allow cascading deletes (using ON DELETE CASCADE).

• Performance Considerations: Deleting large amounts of data can lock tables and impact performance. It may be helpful to delete data in smaller batches, especially in large databases.