DELETE FROM - MySQL

Overview

DELETE FROM removes rows from a database table that meet specific criteria. It’s often used for data cleanup, record deletion, or table modification.

Syntax

DELETE FROM `table_name`
[WHERE `condition`];

Required Parameters:

  • table_name: Name of the table to delete records from.

Optional Parameters:

  • WHERE condition: Specifies the condition to filter records for deletion.

Options/Flags

None.

Examples

Simple Deletion:

DELETE FROM users WHERE id = 10;

Deletion with Multiple Conditions:

DELETE FROM orders WHERE status = 'canceled' AND date < '2023-01-01';

Complex Deletion with Subquery:

DELETE FROM tasks
WHERE task_id IN (SELECT task_id FROM employees WHERE department_id = 10);

Common Issues

  • Accidental Data Loss: Ensure the correct table and records are being targeted before executing DELETE FROM.
  • Foreign Key Constraints: Deleting records from a parent table may affect referenced child tables. Handle foreign key relationships appropriately.

Integration

Combine DELETE FROM with:

  • TRUNCATE TABLE: For faster deletion of all records, but note that this is not undoable.
  • LIMIT: To limit the number of deleted records.
  • ORDER BY: To control the order of record deletion.
  • INSERT INTO: Adds new records to a table.
  • UPDATE: Modifies existing records in a table.
  • TRUNCATE TABLE: Removes all records from a table (irreversible).