MariaDB Delete

Summary: in this tutorial, you will learn how to use the MariaDB delete statement to remove one or more rows from a table.

Introduction to MariaDB delete statement

To delete one or more rows from a table, you use the delete statement:

delete from table_name
[where search_condition];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table from which you want to remove data after the delete from keywords.
• Second, specify which rows to delete by using a condition in a where clause. The where clause is optional. If the where clause is available, the delete statement only removes rows that cause the search_condition to evaluate to true. The delete statement will do nothing if no row from the table causes the search_condition to evaluate to true. In case you omit the where clause, the delete statement will remove all rows from the table.

In a relational database, a table is associated with other tables via foreign key constraints. If you delete rows from the parent table, the corresponding rows from the child tables are automatically deleted if the foreign key constraints use on delete cascade action.

MariaDB delete statement examples

We’ll use the contacts table for the demonstration.

create table if not exists contacts(
    id int auto_increment,
    first_name varchar(50) not null,
    last_name varchar(50) not null,
    full_name varchar(101) 
        as (concat(first_name, ' ', last_name)) virtual,
    phone varchar(100),
    contact_group varchar(50) default 'General',
    primary key(id)
);
Code language: SQL (Structured Query Language) (sql)

The following shows the contents of the contacts table:

select * from contacts;
Code language: SQL (Structured Query Language) (sql)

A) Deleting one row from a table example

The following example uses the delete statement to remove one row from a table:

delete from contacts
where id = 1;
Code language: SQL (Structured Query Language) (sql)

MariaDB issued the following message:

Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 query: 0.000 sec.
Code language: SQL (Structured Query Language) (sql)

The number of affected rows is 1. It means that the row with id 1 has been deleted successfully.

You can verify the deletion by querying data from the contacts table:

select * from contacts
where id = 1;
Code language: SQL (Structured Query Language) (sql)

It returns no row.

B) Deleting multiple rows from a table example

The following statement uses the delete statement to delete contacts whose last name is 'Smith' from the contacts table:

delete from contacts
where last_name = 'Smith';
Code language: SQL (Structured Query Language) (sql)

The number of affected rows is 2:

Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 1 query: 0.000 sec.
Code language: SQL (Structured Query Language) (sql)

The following query finds the contacts whose last name is 'Smith':

select * from contacts
where last_name = 'Smith';
Code language: SQL (Structured Query Language) (sql)

The query returns no rows.

C) Deleting all rows from a table example

The following example uses the delete statement to remove all rows from the contacts table:

delete from contacts;
Code language: SQL (Structured Query Language) (sql)

In this example, the delete statement has no where clause so it deletes all rows from the contacts table.