MySQL DELETE Statement

  1. Overview
  2. MYSQL
  3. MySQL SQL
  4. MySQL DELETE Statement

The DELETE statement in MySQL is used to remove existing records from a table. This command allows you to delete one or more records that meet specific criteria defined by the WHERE clause.

Syntax

Basic Syntax

DELETE FROM table_name
WHERE condition;
  • table_name: The name of the table from which records will be deleted.
  • WHERE condition: A condition to specify which records to delete. If omitted, all records in the table will be deleted.

Delete All Records

If you want to delete all rows in a table but keep the table structure intact (i.e., without deleting the table itself), you can omit the WHERE clause:

Example 1: Delete All Records from a Table

DELETE FROM Customers;

This command will delete all records from the Customers table but leave the table structure, attributes, and indexes intact.

Example Table: Customers

CustomerID CustomerName City Country
1 Alfreds Futterkiste Berlin Germany
2 Ana Trujillo Emparedados y helados México D.F. Mexico
3 Antonio Moreno Taquería Mataderos 2312 Mexico
4 Around the Horn London UK

Example 2: Delete a Specific Record

To delete a specific record based on a condition (e.g., delete the customer with CustomerID 3):

DELETE FROM Customers
WHERE CustomerID = 3;

This will delete the customer with CustomerID 3 from the Customers table.

Important Notes

  1. The WHERE Clause:

    • The WHERE clause is crucial when you want to delete specific records. Omitting the WHERE clause will result in deleting all rows in the table.

  2. Performance Considerations:

    • Deleting large numbers of rows at once can impact performance. For massive deletions, consider using batch deletes or disabling indexes temporarily to speed up the process.

  3. Backup Your Data:

    • Always back up your data before performing delete operations, especially when deleting large amounts of data or running delete statements without a WHERE clause.

  4. Cascading Deletes:

    • If the table has foreign key constraints with ON DELETE CASCADE set, deleting a record from the parent table will also delete corresponding records in child tables.

  5. Truncate vs. Delete:

    • If you need to delete all rows and reset the auto-increment counter (if applicable), you can use the TRUNCATE statement, which is faster than DELETE and does not log individual row deletions.

Example 3: Delete Multiple Records

To delete all customers from Mexico:

DELETE FROM Customers
WHERE Country = 'Mexico';

This command will delete all records where the Country column is equal to Mexico.

The DELETE statement is a powerful tool for removing unwanted data, but it should be used with caution, especially when dealing with large datasets or critical information. Always ensure the condition is correct, and backup data if needed.

Was this article helpful?