MariaDB Delete Row + Examples

In this MariaDB tutorial, we will study the use of MariaDB Delete Row and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB delete row
• MariaDB delete row with foreign key
• MariaDB delete duplicate rows
• MariaDB delete first row
• MariaDB delete oldest row
• MariaDB recover deleted rows
• MariaDB delete line from table

MariaDB Delete Row

Delete statement is used to delete one or more rows from a MariaDB table. The syntax to delete from statement in MariaDB is given below:

DELETE FROM TABLE_NAME [WHERE SEARCH_CONDITION]; 

The syntax explanation is given below:

• First, you need to specify the TABLE_NAME from which you want to delete data by using DELETE FROM statement in MariaDB.
• Second, specify which rows to delete by using condition in a WHERE clause. The where clause is optional. If where clause is available, the delete statement only removes rows that causes SEARCH_CONDITION to be true.

In case, you omitted the WHERE clause, the delete statement will remove all rows from a MariaDB table.

We will use the bankofamerica_customers table for the demonstration.

CREATE TABLE bankofAmerica_customers(
customer_id int PRIMARY KEY,
attrition_flag varchar(30) NOT NULL,
customer_age int,
Gender varchar(5),
Married_Status varchar(30) NOT NULL,
Card_Category varchar(5) NOT NULL);

INSERT INTO bankofamerica_customers VALUES 
(1,'Existing Customer',44,'M','Divorced','Blue'),
(2,'Attrited Customer',35,'F','Married','Platinum'),
(3,'Existing Customer',32,'M','Single','Silver'),
(4,'Attrited Customer',36,'F','UnMarried','Gold'),
(5,'Existing Customer',25,'M','Single','Platinum');

SELECT * FROM bankofAmerica_customers;

As shown in the above output, the user has created a table name by the bank of America for showing customers details in it and the table name is bankofAmerica_customers.

Suppose, the user wants to delete a row from a MariaDB table whose card category is platinum. The sample example for this query is given below:

DELETE FROM bankofAmerica_customers where Card_Category='Platinum';

In the above example, we are trying to delete the customer records from the bankofAmerica_customers table. And we are deleting the rows based on conditions where Card_Category is Platinum. As a result, it has deleted 2 customer_id with the same Card_Category column as Platinum.

If you try to re-look for that row by using the SELECT command then it will not show. The sample example of SELECT command after DELETE FROM command for the Card_Category column as ‘platinum’.

This is how to delete a row in a table MariaDB.

Read How to Create Database in MariaDB

How to Delete Row with Foreign Key in MariaDB

In MariaDB, we might know what is a foreign key. Lets me give a small definition of the foreign key in MariaDB.

In MariaDB, a foreign key is a column or set of columns that reference a column or set of columns of another table. A table that has a foreign key is called a child table and references of the foreign keys in another table are called parent tables.

Let’s, create a table US_studentdetails in MariaDB query as it is shown below:

CREATE TABLE US_studentdetails(
student_id int PRIMARY KEY,
student_name varchar(30) not null,
student_year varchar(10) not null);

DESC US_studentdetails;

INSERT INTO US_studentdetails VALUES
(19101,'James','2nd'),
(19102,'Kandy','1st'),

(19103,'Michael','1st'),
(19104,'Angelia','3nd');

SELECT * FROM US_studentdetails;

Let’s, create a table US_studentexams by using the following MariaDB query as follows:

CREATE TABLE US_studentexams(
exam_id int PRIMARY KEY,
  student_id INT,
  subject_name varchar(8),
  FOREIGN KEY(student_id) 
  REFERENCES US_studentdetails (student_id) 
  ON DELETE CASCADE);

DESC US_studentexams;

INSERT INTO US_studentexams VALUES
(9001,'DBMS','19101'),
(9002,'C.N','19102')
,
(9003,'O.S','19103'),
(9004,'O.S','19104');

SELECT * FROM US_studentexams;

Delete rows from a MariaDB table when there is a foreign key:

Syntax:

DELETE FROM TABLE_NAME WHERE CONSTRAINTS;

Query:

DELETE FROM US_studentdetails where student_id=19102;

Output: After deleting

The rows with student_id=19102 are deleted in both tables.

As we see in the above query, it has deleted all the details of student id as 19,102 from US_studentdetails and US_studentexams table and it is done with the help of foreign key as student_id column put in the table US_studentexams.

This is how to delete row with foreign key in MariaDB.

Read How to Grant User Access to a MariaDB Database

How to Delete Duplicate Rows in MariaDB

In this section, we will show you how to find duplicate rows in a MariaDB table. Once the duplicated rows are identified, we need to clean them to delete our duplicated rows from the table in MariaDB.

The following script creates a US_contacts table and it inserts sample data into the US_contacts table for the demonstration.

DROP TABLE IF EXISTS US_CONTACTS;

CREATE TABLE US_contacts(
contact_id int primary key,
first_name varchar(20) not null,
last_name varchar(30) not null,
email varhcar(50) not null);

INSERT INTO US_contacts VALUES (1,'Carine ','Schmitt','carine.schmitt@verizon.net'),
       (2,'Jean','King','jean.king@me.com'),
       (3,'Peter','Ferguson','peter.ferguson@google.com'),
       (4,'Janine ','Labrune','janine.labrune@aol.com'),
       (5,'Jonas ','Bergulfsen','jonas.bergulfsen@mac.com'),
       (6,'Janine ','Labrune','janine.labrune@aol.com'),
       (7,'Susan','Nelson','susan.nelson@comcast.net'),
     (8,'Zbyszek','Piestrzeniewicz','zbyszek.piestrzeniewicz@att.net'),
       (9,'Roland','Keitel','roland.keitel@yahoo.com'),
       (10,'Julie','Murphy','julie.murphy@yahoo.com'),
       (11,'Kwai','Lee','kwai.lee@google.com'),
       (12,'Jean','King','jean.king@me.com'),
       (13,'Susan','Nelson','susan.nelson@comcast.net'),
       (14,'Roland','Keitel','roland.keitel@yahoo.com');

SELECT * FROM US_contacts;

This query returns data from the US_contacts table by using order by clause and arranging its email column in ascending order.

SELECT * FROM US_contacts ORDER BY email ASC;

By using the above query, we have arranged the duplicated rows in ascending order to check how many rows are there in the table in MariaDB.

As users, we are going to use the inner join clause to remove duplicate rows from the US_contacts table. The sample query is shown below:

DELETE t1 FROM US_contacts t1
INNER JOIN US_contacts t2 
WHERE 
    t1.contact_id < t2.contact_id AND 
    t1.email = t2.email;
    
SELECT * FROM US_contacts;

As the above query explains, it removed duplicated all rows which counted several times in the US_contacts table.

How to delete duplicate rown in MariaDB.

Read How to Add Column in MariaDB

How to delete first row in MariaDB

Let us see, how to delete the first row from a MariaDB table. We just need to use the where clause condition to delete the first row from the MariaDB table.

The syntax to use delete from statement is given below:

DELETE FROM TABLE_NAME where conditions;

The sample example to delete the first row from the bankofAmerica_customers table is given below:

DELETE FROM bankofAmerica_customers where card_category='blue';

Demonstration of deleting the first row of the bankofAmerica_customers table is given below:

DELETE FROM bankofAmerica_customers where married_status='divorced';

As the above query explains, we have deleted the first row from the bankofAmerica_customers table by using the where clause condition and putting the married_status column as “divorced”.

This is how to delete first row in MariaDB.

Read How to Drop Column from MariaDB Table

Delete oldest row in MariaDB

In this section, we will understand how to delete the oldest record from a MariaDB table. Now, when we are working with a production database, we need to find a condition to select the oldest record. And for that condition, we can use Date values.

The following script creates a US_PC_game_sales table and it inserts sample data into the US_PC_game_sales table for the demonstration.

DROP TABLE IF EXIST USA_PC_game_sales;

CREATE TABLE USA_PC_game_sales(
Game_name varchar(20) not null,
Game_sales int not null,
Series varchar(20),
Release_Date varchar(10),
Game_genre varchar(20) not null,
Game_Developer varchar(20) not null,
Game_publisher varchar(20) not null);

INSERT INTO USA_PC_game_sales VALUES
('PU Battlegrounds',42,'',20/12/11,'Battle Royale','PUBG Studios','Krafton'),
('Minecraft',33,'Minecraft',19/11/11,'Sandbox, Survival','Mojang Studios','Mojang Studios'),
('Garrrys Mob',20,'',19/05/06,'Sanbox','Facepunch Studios','Valve'),
('Terraria',17,'',04/05/11,'Action-Adventure','Re-Logic','Re-Logic'),
('World of   Warcraft',14,'Warcraft',19/11/04,'MMGRPG','ArenaNet','NCSoft');

SELECT * FROM USA_PC_game_sales; 

As we see in the above query, date-wise the oldest game is Terraris in the Game_name column.

Let’s delete the oldest record of the table USA_PC_game_sales by using delete from a statement. The use of delete from statement to delete the oldest record by date is given below:

DELETE FROM USA_PC_game_sales where Release_Date='2004/05/11';

If we want to check whether the oldest row has been deleted date wise then try to use a select statement.

In MariaDB, the use of select statement after deletion of the oldest row in the table is given below:

SELECT * FROM USA_PC_game_sales;

This is how to delete oldest row in MariaDB table.

Read How to Create Trigger in MariaDB

MariaDB Recover Deleted Rows

In this section, we will learn how to recover deleted rows from a table in MariaDB. Sometimes users by mistake delete all data of that table X [ just random table_name].

First, use the shortcut to run the MariaDB on the command prompt by demonstration:

mysqldump -u user -p dbname> table_name.sql

The syntax explanation:

• user: Enter the localhost user_name [For eg: root].
• dbname: Enter the database name from which table name by mistake deleted by user.
• >: It is used for exporting the backup file of tablename from the dbname as database name.
• table_name.sql: The table name which by mistake deleted by user and its carried the extension file name by ‘.sql’ in the command prompt.

As we saw in the above query, by this syntax the user has exported table name usa_numericname from the database airbnb_db. Just after using ENTER keyboard, it will ask the user for the localhost password as shown in the above diagram, the Enter password is Test@12345.

[Note:] Don’t use the semicolon[;] sign after the end of the query as it will show an error which is shown below:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual corresponds to your MariaDB server on for the right syntax to use near 'mysqldump -u root -p airbnb_db> usa_numericname.sql' at line 1.

As of now, the backup of table usa_numericname is done in the installation drive in the MariaDB as “F:\Program Files\MariaDB 10.5\bin” path file. The backup image of table usa_numericname table is given below:

Once the backup of table_name as usa_numericname has been done. There is no use of database airbnb_db, so let it drop that database airbnb_db from MariaDB by using the following query given below:

DROP DATABASE AIRBNB_DB;

SHOW DATABASES;

The show databases have been used to check whether the database airbnb_db has been removed from the database or not, it is shown by the image below:

Now, create a new database with the same database name airbnb_db or any other name by user choice. The query for creating a new database is given below:

CREATE DATABASE airbnb_db;

SHOW airbnb_db;

let’s import the backup table name as usa_numericname from old database_name to new database_name as airbnb_db. Once the new database has been created run the query ‘MySQL -u root -p airbnb_db < usa_numericname’ without a semi-colon[;] sign in it.

mysql -u root -p airbnb_db < usa_numericname.sql

[Note:] The less sign [<] is used to import the usa_numericname table to the new database airbnb_db from the old database airbnb_db.

Don’t forget to enter the password of localhost in the “Enter password:” section and after completing the query don’t write the semicolon sign in it otherwise it will provide the error.

The error may arise after using semi-colon [;] sign is given below:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual corresponds to your MariaDB server on for the right syntax to use near 'mysqldump -u root -p airbnb_db> usa_numericname.sql' at line 1.

Once the import of backup table file usa_numericname is done to the new database file airbnb_db, to check the tables added to the new database write the below query as for demonstration:

CREATE DATABASE airbnb_db;

USE airbnb_db;

SHOW TABLES FROM airbnb_db;

Now, let’s check the new table usa_numericname from the new database airbnb_db after the backup is done from the old database airbnb_db. The query is shown below:

SELECT * FROM usa_numericname;

This is how to recover delete rows from a table in MariaDB.

Read How to Create Function in MariaDB

Delete Line from Table in MariaDB

In MariaDB, the sub-topic ‘delete line from table’ refers to ‘delete row from table’. The syntax to delete the first row from the table:

DELETE FROM TABLE_NAME where [CONDITIONS];

The following script creates a USA_OlympicRoster table and it inserts sample data into the USA_OlympicRoster table for the demonstration.

DROP TABLE IF EXISTS USA_OlympicRoster;

CREATE TABLE USA_OlympicRoster(
player_id int primary key,
player_name varchar(20) not null,
Current_Age int,
School varchar(30) not null,
HomeTown varchar(40) not null);

DESC USA_OlympicRoster;

INSERT INTO USA_OlympicRoster VALUES
(1,'Bam Abedayo',24,'University of Kentucky','High Point, NC'),
(2,'Devin Booker',25,'University of Kentucky','Moss Point,MS'),
(3,'Kevin Durant',33,'University of Texas','Washington, DC'),
(4,'Jerami Grant',27,'Syracuse University','Bowie, MD'),
(5,'Graymond Green',31,'Michigan State University','Saginaw,MI');

SELECT * FROM USA_OlympicRoster;

Lets, delete one row from the USA_OlympicRoster table as shown in the below query:

DELETE FROM USA_OlympicRoster where player_name='Bam Abedayo';

As we saw from the above query, after using the delete from statement we have tried to search it Ban Abedayo name in the player_name column. The player name will show missing from the table.

Then try to run a select statement to check whether the delete statement has removed the player name as Ban Abedayo from the player_name column from the USA_OlympicRoster table.

AFTER DELETE QUERY:

SELECT * FROM USA_OlympicRoster;

Related MariaDB tutorials:

• MariaDB Full Outer Join
• MariaDB Union Operator
• MariaDB Cast with Examples
• MariaDB ENUM – Helpful Guide
• How to import CSV files in MariaDB
• MariaDB Select Into + Examples
• Replace Function in MariaDB

In this MariaDB tutorial, we have learned about how to delete a row from a table in MariaDB by using some examples. Moreover, we have also covered the following topics in this tutorial as given below:

• MariaDB delete row
• MariaDB delete row with foreign key
• How to delete duplicate rows in MariaDB
• MariaDB delete first row
• How to delete oldest row in MariaDB
• MariaDB recover deleted rows
• How to delete line from table in MariaDB