MySQL DELETE

What is DELETE Statement and Why is it Used?

If you want to delete the data in a specific table, type DELETE FROM table_name WHERE condition; It will delete the matching data from the table.

The data, records, multiple records, or rows which are not needed for the table unnecessarily consume memory, so the DELETE statement is used to delete them. With the use of the WHERE clause, you can choose specific records by applying a condition.

Syntax:

DELETE FROM table_name
WHERE condition;

DELETE with WHERE Clause

The specific records you want to delete from a table are chosen using the WHERE clause.

If the condition inside the WHERE clause matches a record, that record is deleted and keeps the table structure the same.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20

If you want to delete the studentA whose Student_ID is 102, use:

Query:

DELETE FROM StudentA
WHERE Student_ID = 102;

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
103	Amit raj	20

DELETE Multiple Rows

The DELETE statement can be used to delete multiple records from a table. If the condition inside the WHERE clause matches multiple records, all matching records are deleted and not the table structure.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20
104	Neha	24

If you want to delete the students whose Age is 24, use:

Query:

DELETE FROM StudentA
WHERE Age = 24;

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
103	Amit raj	20

DELETE All Rows

The DELETE statement can be used to delete all records from a table. When the WHERE clause is not used, the statement deletes all records from the table and not the table structure.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20

If you want to delete all records from the StudentA table, use:

Query:

DELETE FROM StudentA;

Output:

After executing the statement, the result shows the StudentA table with no records:

Student_ID	Name	Age
No records

The StudentA table structure remains available.

DELETE with Different Conditions

The DELETE statement can be used with different conditions in the WHERE clause to delete specific records from a table. The condition helps to choose the records that you want to delete.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20
104	Neha	24

If you want to delete the student whose Age is greater than 22, use:

Query:

DELETE FROM StudentA
WHERE Age > 22;

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
103	Amit raj	20

DELETE with LIMIT

The LIMIT clause can be used with the DELETE statement to limit the number of records that are deleted from a table. It helps to delete only a specific number of records.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20
104	Neha	24

If you want to delete only one record where Age is 24, use:

Query:

DELETE FROM StudentA
WHERE Age = 24
LIMIT 1;

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
103	Amit raj	20
104	Neha	24

DELETE with ORDER BY

The DELETE statement can use the ORDER BY clause to arrange the matching records in a specific order before deleting them.

It is generally used with the LIMIT clause to control which matching records are deleted.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20
104	Neha	26

If you want to delete the student with the highest Age, use:

Query:

DELETE FROM StudentA
ORDER BY Age DESC
LIMIT 1;

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20

DELETE Using Multiple Conditions

Multiple conditions can be used in the WHERE clause of the DELETE statement to choose specific records from a table. Both the AND and OR operators can be used to combine multiple conditions.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20
104	Neha	24

If you want to delete the student whose Age is 24 and Student_ID is 102, use:

Query:

DELETE FROM StudentA
WHERE Age = 24 AND Student_ID = 102;

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
103	Amit raj	20
104	Neha	24

DELETE Based on NULL Values

The DELETE statement can be used with IS NULL or IS NOT NULL to delete records based on NULL values in a column. The IS NULL condition selects records where the column has no value.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	NULL
103	Amit raj	20

If you want to delete the student whose Age has a NULL value, use:

Query:

DELETE FROM StudentA
WHERE Age IS NULL;

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
103	Amit raj	20

DELETE Using IN Operator

The IN operator can be used with the DELETE statement to choose multiple specific values in the WHERE clause. It deletes the records where the column value matches any value given in the IN list.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20
104	Neha	24

If you want to delete the students whose Age is 20 or 24, use:

Query:

DELETE FROM StudentA
WHERE Age IN (20, 24);

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22

DELETE Using BETWEEN Operator

The BETWEEN operator can be used with the DELETE statement to choose records whose values are within a specific range. The starting and ending values are both included by the BETWEEN operator.**

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20
104	Neha	26

If you want to delete the students whose Age is between 22 and 26, use:

Query:

DELETE FROM StudentA
WHERE Age BETWEEN 22 AND 26;

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
103	Amit raj	20

DELETE Using LIKE Operator

The LIKE operator can be used with the DELETE statement to choose records based on a specific pattern in a column. It is useful when you want to delete records where text values match a given pattern.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit	20
104	Neha	24

If you want to delete the students whose Name starts with A, use:

Query:

DELETE FROM StudentA
WHERE Name LIKE 'A%';

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
104	Neha	24

DELETE Using Subquery

The DELETE statement can use a subquery to choose records based on the result of another query.The DELETE statement removes the matching record from the table after the subquery first finds the required value.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20

Suppose we have an AgeData table:

Student_ID	Age
102	24

If you want to delete the student from StudentA whose Student_ID is available in the AgeData table, use:

Query:

DELETE FROM StudentA
WHERE Student_ID IN (
    SELECT Student_ID
    FROM AgeData
);

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
103	Amit raj	20

DELETE from Multiple Tables

In MySQL, the DELETE statement can delete records from more than one table at the same time. It can use a JOIN to find the related records that you want to delete

Example:

Suppose we have a StudentA table:

Student_ID	Name
101	Ranjeet
102	Priti
103	Amit raj

Suppose we also have a StudentAge table:

Student_ID	Age
101	22
102	24
103	20

If you want to delete the student whose Student_ID is 102 from both tables, use:

Query:

DELETE StudentA, StudentAge
FROM StudentA
JOIN StudentAge
ON StudentA.Student_ID = StudentAge.Student_ID
WHERE StudentA.Student_ID = 102;

Output:

After executing the statement, the result shows the StudentA table:

Student_ID	Name
101	Ranjeet
103	Amit raj

The result shows the StudentAge table:

Student_ID	Age
101	22
103	20

DELETE vs TRUNCATE

The DELETE and TRUNCATE statements are both used to remove records from a table, but they work differently. DELETE can remove specific records by using the WHERE clause, while TRUNCATE removes all records from the table. The table structure remains available after using both statements.

Example:

Suppose we have a StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
102	Priti	24
103	Amit raj	20

If you want to delete only the record where Student_ID is 102, use DELETE:

Query:

DELETE FROM StudentA
WHERE Student_ID = 102;

Output:

After executing the DELETE statement, the result shows the StudentA table:

Student_ID	Name	Age
101	Ranjeet	22
103	Amit raj	20

If you want to delete all records from the StudentA table, use TRUNCATE:

Query:

TRUNCATE TABLE StudentA;

Output:

After executing the TRUNCATE TABLE statement, the result shows the StudentA table with no records:

Student_ID	Name	Age
No records

The StudentA table structure remains available.