MySQL - DELETE Statement



MySQL DELETE Statement

The DELETE statement from MySQL is used to delete records from a MySQL table. To remove specific records, you need to use WHERE clause along with it. If you need to define common table expressions accessible with the delete you can use the WITH clause.

Syntax

Following is the syntax of the DELETE statement −

DELETE FROM table_name [WHERE Clause]

Example

Assume we have created a table in MySQL with name EMPLOYEES as −

CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
);

And if we have inserted 4 records in to it using INSERT statements as −

INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000);

Following MySQL statement deletes the record of the employee with FIRST_NAME "Mac".

DELETE FROM EMPLOYEE WHERE FIRST_NAME = 'Mac';

Verification

If you retrieve the contents of the table, you can see only 3 records since we have deleted one.

select * from EMPLOYEE;

Output

The above query produces the following output −

FIRST_NAME LAST_NAME AGE SEX INCOME
Krishna Sharma 20 M 2000
Raj Kandukuri 21 M 7000
Ramya Ramapriya 25 F 5000

If you execute the DELETE statement without the WHERE clause all the records from the specified table will be deleted.

DELETE FROM EMPLOYEE;

Verification

If you retrieve the contents of the table, you will get an empty set as shown below −

select * from EMPLOYEE;
Empty set (0.00 sec)

Using where clause

Assume we have created a table with name Sales in MySQL database using CREATE TABLE statement as shown below −

CREATE TABLE sales(
   ID INT,
   ProductName VARCHAR(255),
   CustomerName VARCHAR(255),
   DispatchDate date,
   DeliveryTime time,
   Price INT,
   Location VARCHAR(255)
);

Following query inserts a row in the above created table −

INSERT INTO SALES values
(1, 'Key-Board', 'Raja', DATE('2019-09-01'), 
TIME('11:00:00'), 7000, 'Hyderabad'),
(2, 'Earphones', 'Roja', DATE('2019-05-01'), 
TIME('11:00:00'), 2000, 'Vishakhapatnam'),
(3, 'Mouse', 'Puja', DATE('2019-03-01'), 
TIME('10:59:59'), 3000, 'Vijayawada'),
(4, 'Mobile', 'Vanaja', DATE('2019-03-01'), 
TIME('10:10:52'), 9000, 'Chennai'),
(5, 'Headset', 'Jalaja', DATE('2019-04-06'), 
TIME('11:08:59'), 6000, 'Goa');

Following query deletes the records of the Sales table with the price value greater than 6000, using the SELECT statement −

DELETE FROM SALES WHERE PRICE>6000;

Verification

You can verify the contents of this table after deletion using the SELECT statement as shown below −

select * from sales;

Output

Following is the output of the above query −

ID Product Name Customer Name Dispatch Date Delivery Time Price Location
2 Earphones Roja 2019-05-01 11:00:00 2000 Vishakhapatnam
3 Mouse Puja 2019-03-01 10:59:59 3000 Vijayawada
5 Headset Jalaja 2019-04-06 11:08:59 6000 Goa

Using the Order by clause

You can also use the ORDER BY clause along with the DELETE statement. If you do so, the rows in the table are deleted in the specified order.

DELETE FROM SALES WHERE PRICE > 2000 ORDER BY DeliveryTime LIMIT 2;

You can verify the contents of this table after deletion using the SELECT statement as shown below −

SELECT * FROM SALES;

Output

The above mysql query will generate the following output −

ID Product Name Customer Name Dispatch Date Delivery Time Price Location
1 Key-Board Raja 2019-09-01 11:00:00 7000 Hyderabad
2 Earphones Roja 2019-05-01 11:00:00 2000 Vishakhapatnam
5 Headset Jalaja 2019-04-06 11:08:59 6000 Goa

Multi-table deletes

You can delete records from more than one table using the DELETE statement. For this you need to specify the names of the tables in the statement.

Example

Suppose we have created a table with name EMPLOYEE using the following CREATE statement −

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);

Now, let us insert two records into the EMPLOYEE table −

INSERT INTO Employee VALUES
(101, 'Ramya', 'Rama Priya', 27, 'F', 9000, 101),
(102, 'Vinay', 'Bhattacharya', 20, 'M', 6000, 102);

Let us create another table and populated it as −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);

Now, let us insert some records into the CONTACT table −

INSERT INTO CONTACT (ID, EMAIL, CITY) VALUES
(101, 'ramya@mymail.com', 'Hyderabad'),
(102, 'vinay@mymail.com', 'Vishakhapatnam');

Following query deletes records from the above created tables −

DELETE EMPLOYEE, CONTACT FROM EMPLOYEE INNER JOIN CONTACT ON 
CONTACT.id = EMPLOYEE.id WHERE EMPLOYEE.INCOME<7000;

Verification

If you verify the contents of these tables you can find the record with id value 102 (INCAME value less than 7000) is removed from the two tables −

SELECT * FROM CONTACT;

Following is the output of the above query −

ID EMAIL PHONE CITY
101 ramya@mymail.com NULL Hyderabad

Now, let's verify the contents of the EMPLOYEE table:

SELECT * FROM EMPLOYEE;

Output

The above mysql query will generate the following output −

ID FIRST_NAME LAST_NAME AGE SEX INCOME CONTACT
101 Ramya Rama Priya 27 F 9000 101
Advertisements