MySQL DELETE Statement

In DBMS, CRUD operations (Create, Read, Update, Delete) are fundamental for managing data effectively. Among these, the Delete operation, which involves deleting data from a database, plays a crucial role.

Here, we will cover how MySQL DELETE Statement works, exploring its syntax and examples.

DELETE Statement

The MySQL DELETE Statement is used to delete one or more existing records from a table. It is commonly used with the WHERE or JOIN clause.

• It is a Data Manipulation Language (DML) statement.
• We cannot generally ROLLBACK (undo) after performing the DELETE statement.
• We can either delete the entire data in a table using DELETE or else we can also delete only some specific rows from the table.

Syntax

DELETE FROM table_name WHERE condition;

Note: Be careful when using the DELETE statement in MySQL. Always use a DELETE statement with a WHERE clause. 

Demo MySQL Database

We will use the following table for our examples:

ID	Name	Department	Location
12	Ravi	IT	Hyderabad
15	Kiran	MECH	Mysore
18	Navya	CSE	Hyderabad
20	Rahul	CIVIL	Chennai
22	Alex	ECE	Bengaluru
24	Bob	IT	Vizag

To create this table in your system write the following MySQL queries:

MySQL

CREATE TABLE students (
  ID int NOT NULL,
  Name varchar(255) NOT NULL,
  Department varchar(255) NOT NULL,
  Location varchar(255) NOT NULL,
  PRIMARY KEY (ID)
);

INSERT INTO students (ID, Name, Department, Location) VALUES
  (12, 'Ravi', 'IT', 'Hyderabad'),
  (15, 'Kiran', 'MECH', 'Mysore'),
  (18, 'Navya', 'CSE', 'Hyderabad'),
  (20, 'Rahul', 'CIVIL', 'Chennai'),
  (22, 'Alex', 'ECE', 'Bengaluru'),
  (24, 'Bob', 'IT', 'Vizag');

DELETE Statement Examples

Let’s look at some MySQL DELETE statement examples to understand its working and also cover different use cases of DELETE statement.

Example 1: DELETE Statement with WHERE Clause

We can use DELETE statement with WHERE clause, to specifically delete some data from the table. In this example, we will delete the rows of students who belongs to IT or CSE and from Hyderabad.

Query:

DELETE FROM STUDENT WHERE (Department ='IT' or Department ='CSE') and location ='Hyderabad';

Output:

MySQL DELETE Example With WHERE Condition.

We have deleted Student details of Ravi and Navya as they are satisfying the conditions of location as ‘Hyderabad’ and Department of Ravi is ‘IT’ and Department of Navya is ‘CSE’.

Example 2: Delete the Entire Data From the Table

We can delete the entire data from the table, by not using the WHERE clause. Consider the same Student table above with 6 entries and columns ID, Name, Department, location columns.

Query:

DELETE FROM Student;

Output:

Entries in the table have been deleted completely when we do not use the WHERE clause, As you can see in the output, there are no rows left.

DELETE statement without WHERE clause.

MySQL DELETE With LIMIT Clause

In MySQL, Using DELETE with LIMIT will allow us to specify the maximum number of records that need to be deleted from the table.

• DELETE with LIMIT without WHERE condition: It deletes the no. of initial rows as specified by the LIMIT clause.
  
• DELETE with LIMIT with WHERE condition: It first applies WHERE condition, then after filtering out, it deletes those many initial rows specified by the LIMIT clause.
  
• DELETE with LIMIT with ORDER BY clause: It selects the rows with matching condition if there is a WHERE condition, then sorts the rows based on specified column, and then it deletes the maximum rows specified by the limit clause.

ORDER OF EXECUTION

FROM

->WHERE (Optional)

-> ORDER BY (Optional)

-> LIMIT

Example: To Delete the 2 Student Records Who Secured the Least Marks in the Class

Output Before Deletion for DELETE With LIMIT Clause.

Query:

DELETE FROM Marks WHERE marks<=50 ORDER BY MARKS ASC LIMIT 2;

As we can see, first the query selected rows which have marks<=50, i.e. “Rahul”, “Gill”, “Shami”, and “Rahane”. Then it ordered the rows in ascending order based on the marks i.e.

Shami, 34
Rahul, 47
Rahane, 48
Gill, 50

Output:

Now, it applies the limit condition and deletes the first 2 rows. i.e., “Shami” and “Rahul” as shown below.

Output After Deletion for DELETE With LIMIT Clause.

MySQL DELETE with JOIN Clause

MySQL allows us to delete rows from multiple tables based on the matching condition. We use JOIN Clause to first joins the tables based on the join condition and then deletes the rows from both tables.

If only one table is specified, then rows from only one table are deleted.

Sequence of Execution:

FROM->

JOIN->

WHERE->

DELETE.

Example

Initially, we created a CUSTOMERS table with columns id, name, and contact.

Customers Table.

Next, we have created another table ORDERS with columns customer_id, order_id, order_name, and order_price. It contains all the orders placed by the customers in the customers table.

Orders Table.

Problem Statement: We want to delete the rows of customer with customer Id “156” in CUSTOMERS table and his associated orders in ORDERS table.

Query:

DELETE  customers, orders 
FROM customers 
INNER JOIN orders ON customers.id=orders.customer_id 
WHERE  customers.id=156;

Output:

As we can see in the below output, the customer record with id “156” is deleted in the customers table and orders related with customer_id “156” are also deleted in the Orders table.

The query will first join the tables based on the join condition, then it will execute the WHERE condition and select those rows whose id is “156”. Finally, it will delete those rows from both the table.

Note: If you omit customers or orders after the DELETE keyword, then rows will be deleted only from the specified table.

Customers:

Customers Table After DELETE with JOIN

Orders:

Orders Table After DELETE with JOIN.

Key TakeAways

• The DELETE statement returns the number of rows affected (deleted) which can be helpful for verification.
• Deleted data is typically unrecoverable (use with caution!).
• Always back up your database before using DELETE.
• Consider using logical deletion (setting a flag instead of complete removal) for situations where you might need to recover data later.