MariaDB DELETE Statement


Objectives

  • Understanding of MariaDB DELETE Statement

  • MariaDB DELETE Single Record

  • MariaDB DELETE Multiple Records

  • MariaDB DELETE ALL Records


Create database and table and insert data if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)

--- Remove old Data
TRUNCATE person;

--- Insert data into table if not exist
INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 1, 5000),
	('John', 'Doe', 'john@pf.local', 19, 100),
	('Tahsin', NULL, 'tahsin@pf.local', 10, 150),
	('Jane', 'Doe', 'jane@gmail.com', 26, 300),
	('Rakib', 'Mia', 'rakib@bf.local', 24, 200),
	('Sagor', 'Sowrov', 'email10@bf.local', 20, 250),
	('Touhid', NULL, 'hmtmcse.com@gmail.com', 30, 500);


Understanding of MariaDB DELETE Statement

Syntax

DELETE FROM table_name WHERE conditions;
Here

  • DELETE : DELETE keyword responsible to delete record from table.

  • FROM : FROM is a keyword of MariaDB, which indicate the table name from of the database.

  • table_name : Where data will delete.

  • WHERE : WHERE is a keyword of MariaDB as well, which indicate what will be the conditions of data select.

  • conditions : Equal, Not equal, Greater than, Less than etc. conditions.


MariaDB DELETE Single Record

Problem: Delete John Doe which id is 2

Syntax

DELETE FROM table_name WHERE conditions;

Example

DELETE FROM person WHERE id = 2;

Output

Query OK, 1 row affected (0.380 sec)


MariaDB DELETE Multiple Records

Problem: Delete all record whose lastname is Mia

Syntax

DELETE FROM table_name WHERE conditions;

Example

DELETE FROM person WHERE last_name = 'Mia';

Output

Query OK, 2 rows affected (0.226 sec)


MariaDB DELETE ALL Records

Problem: Delete all data from person

Syntax

DELETE FROM table_name WHERE conditions;

Example

DELETE FROM person;

Output

Query OK, 5 rows affected (0.109 sec)

Note : Without where condition delete is very risky, it will delete your all data, so before run the command make sure you are confident enough to run this command and the query is error free.


MariaDB DELETE Statement Video tutorial

Share