MySQL DELETE Statement

In DBMS, CRUD operations (Create, Read, Update, Delete) are essential for effective data management. The Delete operation is crucial for removing data from a database. This guide covers the MySQL DELETE statement, exploring its syntax and providing examples.

Understanding how DELETE works helps ensure accurate and safe data removal, preventing accidental loss and maintaining data integrity.

DELETE Statement

The MySQL DELETE statement deletes 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. Generally, you cannot ROLLBACK (undo) after performing the DELETE statement. You can delete the entire table data using DELETE or delete only specific rows.

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

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

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');

Output:

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:

Explanation: 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, and location columns.

Query:

DELETE FROM Student;

Output:

Explanation: 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.

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

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.

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.

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.

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:

Orders:

Conclusion

The MySQL DELETE statement is a powerful tool for managing database records. It can delete specific rows or entire tables, but its changes are often permanent, so caution is necessary. Always use a WHERE clause to avoid unintentional deletions, back up your data beforehand, and consider logical deletion methods for safer data management. Understanding its syntax and use cases ensures efficient and safe data handling.