MariaDB Delete From Statement [With 9 Amazing Examples]

In this MariaDB tutorial, we will look at the MariaDB DELETE FROM statement and look at several examples. There are lists of the topic that comes under discussion:

• MariaDB Delete From
• MariaDB Delete From Table
• MariaDB Delete From Join
• MariaDB Delete From Multiple Tables
• MariaDB Delete From Select
• MariaDB Delete From Where
• MariaDB Delete From Inner Join
• MariaDB Delete From SubQuery
• MariaDB Delete From 2 Tables

MariaDB Delete From

Let’s go through the MariaDB Delete From statement in-depth in this sub-topic, which is explained with syntax and an illustrative example.

We can delete many rows from a table using the MariaDB DELETE FROM statement. Let’s see the syntax of the DELETE FROM statement in the following query:

SYNTAX:

DELETE FROM TABLE_NAME
WHERE [CONDITION];

The syntax explanation from the above query:

• Using the DELETE FROM command, we must first specify the table name from which we want to remove data.
• Second, use the WHERE condition clause to determine which rows should be eliminated. It’s also optional. The DELETE FROM statement eliminates rows from the table if the WHERE condition evaluates to TRUE if it is accessible.
• If the WHERE clause is not included in the DELETE FROM condition, the DELETE FROM statement will delete all rows from the table.

The table is linked to another table in a relational database using foreign key restrictions. If the foreign key constraints employ the ON DELETE CASCADE action in the table, when we delete rows from the parent table, the matching rows from the child table are also erased.

If we want to delete all the records from the STATES_OF_USA table by using the DELETE FROM statement, then use the below query:

DELETE FROM STATES_OF_USA;

The DELETE FROM statement will remove all records from the STATES_OF_USA table. If we want to check any records left in the STATES_OF_USA table, then use the SELECT statement.

Now, let’s have a look at the DELETE FROM statement for deleting a single row by the following query:

EXAMPLE:

DELETE FROM STATES_OF_USA 
WHERE STATE_ID=31;

In this above query, we are deleting all records of one row whose STATE_ID is 31 from the STATES_OF_USA table by using the DELETE FROM statement.

If we want to verify the deletion of the above query from the STATES_OF_ID table, then use the below query for a check as shown below:

SELECT * FROM STATES_OF_USA
WHERE STATE_ID=31;

Hence, the query will be executed of the SELECT statement if we try to get the output. Then it will show no output as the DELETE FROM statement has already removed all details of STATE_ID as 31 from the STATES_OF_USA table.

Read: MariaDB COUNT Function

MariaDB Delete From Table

In this sub-topic, we will learn how to use the MariaDB DELETE FROM statement to delete multiple records from the table and which is explained with the help of an illustrated example.

EXAMPLE:

DELETE FROM STATES_OF_USA 
WHERE STATE_ID=25 AND STATE_ID=28;

In the preceding query, we have deleted two rows from the STATES_OF_USA table where the STATE_ID is 25 and 28 by the WHERE clause. So, if the STATES_OF_USA table contains these two records. Then it will be deleted otherwise the query will be executed and no records will be deleted from the STATES_OF_USA table by using the DELETE FROM statement.

Read: MariaDB JSON Data Type

MariaDB Delete From Join

in this section, we will learn how to use one of the JOIN clauses in the DELETE FROM statement and which is explained with the help of an illustrated example.

DELETE EMPLOYEE,MACK_DATA FROM EMPLOYEE
INNER JOIN 
MACK_DATA ON EMPLOYEE.EMP_ID=MACK_DATA.DATA_ID
WHERE MACK_DATA.DATA_ID=8;

As we see in the above query, we are deleting records from both the tables EMPLOYEE and MACK_DATA table on the basis of the RIGHT JOIN clause.

As both joining conditions meet EMP_ID on the EMPLOYEE table and DATA_ID on the MACK_DATA table. So it removes one row of the DATA_ID as 8 from the MACK_DATA by using the DELETE FROM statement.

Read: How to load files into MariaDB

MariaDB Delete From Multiple Tables

Here we will understand the use of the DELETE JOIN statement and discuss how to delete data from many tables at once.

We learned how to delete rows from several tables using the MariaDB DELETE FROM statement in the previous subtopic:

• On several tables, we utilize the DELETE FROM statement.
• A single DELETE FROM statement with an ON DELETE CASCADE structural action for the foreign key in the second table across numerous connected tables.

Using the INNER JOIN or LEFT JOIN clause on the DELETE FROM command, this lesson shows us a more flexible way to delete records from many tables. The following query shows how to delete rows from the EMPLOYEE and MACK_DATA tables using the DELETE FROM statement with the LEFT JOIN clause:

EXAMPLE:

DELETE EMPLOYEE,MACK_DATA FROM EMPLOYEE
LEFT JOIN 
MACK_DATA ON EMPLOYEE.EMP_ID=MACK_DATA.DATA_ID
WHERE EMPLOYEE.EMP_ID=1;

In the preceding query, we have deleted two records from the EMPLOYEE and MACK_DATA table by using the DELETE statement with the LEFT JOIN clause for the resultset.

Read: MariaDB Reserved Words

MariaDB Delete From Select

In this sub-topic, there is no certain method to delete from select in the same query in the MariaDB.

Currently, we can’t delete from a table and select from the same table in the subquery. We need the DELETE privilege on the table to delete rows from it. We need only the SELECT privilege for any column that can be read such as those named in the WHERE clause in the EMPLOYEE table.

If we try to use the DELETE FROM statement and the SELECT statement in the subquery, then it will throw an error. Let’s see the illustrated example in the subquery from the following method:

EXAMPLE:

DELETE FROM EMPLOYEE 
WHERE (SELECT * FROM EMPLOYEE 
WHERE EMP_ID=4);

As we see in the preceding query, we are trying to delete all records from the EMPLOYEE table by using the DELETE FROM statement based on the SELECT statement in the subquery. As it leads to errors in the MariaDB. If we want, we can separate both the query from each other and perform their execution and give the result as per user requirement.

Read: MariaDB Date_Format

MariaDB Delete From Where

In this sub-topic, we will learn how to use the WHERE condition with the DELETE FROM statement and which is explained with the help of an illustrated example.

In MariaDB, the WHERE condition is used from where we want to retrieve data in the table. If the condition sets to be TRUE in the table, then it will delete records as per the condition from the table by using the DELETE FROM statement.

If the WHERE condition is true and the rows which are going to be deleted are not up to mark as per table data. Then the query will be executed but no records will be deleted from the table by using the DELETE FROM statement.

Let’s see a quick example of using the WHERE condition in the DELETE FROM statement by the following query:

EXAMPLE:

DELETE FROM STATES_OF_USA 
WHERE STATE_ID=3;

As we see in the above query, we have deleted one row from the STATES_OF_USA table based on the condition if the state_id turns out to be 3. Then it will delete all records from the STATES_OF_USA table by using the DELETE FROM statement.

Read: MariaDB Rename Table

MariaDB Delete From Inner Join

With the MariaDB DELETE FROM statement, we’ll talk about and learn about the INNER JOIN clause, which is explained with syntax and an example.

The INNER JOIN is the most popular sort of join in MariaDB, and it returns all rows from the table if the join condition is met. Let’s see the example of the DELETE FROM statement with the INNER JOIN clause by the following query:

EXAMPLE:

DELETE EMPLOYEE,MACK_DATA FROM EMPLOYEE
INNER JOIN 
MACK_DATA ON EMPLOYEE.EMP_ID=MACK_DATA.DATA_ID
WHERE EMPLOYEE.EMP_ID=5;

Using the DELETE statement with the INNER JOIN clause for the resultset, we deleted two records from the EMPLOYEE and MACK_DATA tables in the preceding query.

Read: MariaDB ENUM

MariaDB Delete From SubQuery

In this section, we’ll learn how to use the DELETE FROM statement in a subquery, which will be demonstrated with an example.

In MariaDB, the subquery refers to a query inside another query. Here is the example of using the subquery with the DELETE FROM statement by the following query:

EXAMPLE:

DELETE e.*
 FROM states_of_usa e
WHERE state_id IN (SELECT state_id
FROM (SELECT state_id
 FROM states_of_usa
WHERE state_shortform = 'AL' AND state_population = 'HIGH') x);

In this preceding query, we are deleting records from DELETE FROM statement with the SELECT statement in the subquery. And on the basis of the WHERE condition, it will delete first records of the STATE_SHORTFORM as AL and STATE_POPULATION as HIGH and also the STATE_ID column also.

This means that it will use the SELECT statement to show the first record from the STATES_OF_USA table then it will delete it from the STATE_OF_USA table by using the DELETE FROM statement. If we want to check whether the query executed has really deleted the first records or not, then use the SELECT statement.

Read: MariaDB vs Postgres

MariaDB Delete From 2 Tables

We’ll utilise the JOIN clause with the DELETE FROM statement on the two MariaDB tables, as the sub-topic states, and it’ll be discussed with an example.

DELETE EMPLOYEE,MACK_DATA FROM EMPLOYEE
INNER JOIN 
MACK_DATA ON EMPLOYEE.EMP_ID=MACK_DATA.DATA_ID
WHERE EMPLOYEE.EMP_ID=5;

• As we see in the above query, we are deleting records from both the tables EMPLOYEE and MACK_DATA table on the basis of the RIGHT JOIN clause.
• As both joining conditions meet EMP_ID on the EMPLOYEE table and DATA_ID on the MACK_DATA table.
• So it removes one row of the EMP_ID as 7 from the EMPLOYEE by using the DELETE FROM statement.

Also, take a look at some more MariaDB tutorials.

• MariaDB If Null + Examples
• MariaDB Greatest Function
• MariaDB Logs – Helpful Guide
• MariaDB Temporary Table
• MariaDB Reset Root Password
• MariaDB Not Equal Operator

So, in this MariaDB tutorial, we understood how to use the MariaDB DELETE FROM statement and also look at some sample examples. There are lists of the topic that comes under discussion:

• MariaDB Delete From
• MariaDB Delete From Table
• MariaDB Delete From Join
• MariaDB Delete From Multiple Tables
• MariaDB Delete From Select
• MariaDB Delete From Where
• MariaDB Delete From Inner Join
• MariaDB Delete From SubQuery
• MariaDB Delete From 2 Tables