MySql Delete

DELETE Statement
Description

DELETE statement is used to delete a single record or multiple records from a table in MYSQL

Syntax

DELETE FROM table
WHERE conditions;

However, the full syntax for the DELETE statement in MYSQL is:

DELETE [ TOP (top_value) [ PERCENT ] ]
FROM table
WHERE conditions;

Parameters or Arguments

table is the table that you wish to delete records from.

conditions are conditions that must be met for the records to be deleted.

TOP (top_value) is optional. If specified, it will delete the top number of rows in the result set based on top_value. For example, TOP(10) would delete the top 10 rows matching the delete criteria.

PERCENT is optional. If PERCENT is specified, then the top rows are based on a top_value percentage of the total result set (as specfied by the PERCENT value). For example, TOP(10) PERCENT would delete the top 10% of the records matching the delete criteria.

Note

  • You do not need to list fields in the MYSQL DELETE statement since you are deleting the entire row from the table.
Example – Using One condition

Let’s look at a simple MYSQL DELETE query example, where we just have one condition in the DELETE statement.

For example:

DELETE FROM emp
WHERE empid=1001;

This MYSQL DELETE example would delete all records from the employees table where the first_name is ‘Sarah’.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SELECT statement before performing the delete.

SELECT count( * )
FROM emp;
Example – Using Two conditions

Let’s look at a MYSQL DELETE example, where we just have two conditions in the DELETE statement.

For example:

DELETE FROM emp
WHERE lastName = 'Murphy'
AND salary >=20000;

This MYSQL DELETE example would delete all records from the emp table where the lastName is ‘Murphy’ and the empid is greater than or equal to 80.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SELECT statement before performing the delete.

 SELECT count( * )
FROM emp
WHERE salary >=20000
AND manager =1002;
Example – Using TOP keyword

Let’s look at a MYSQL DELETE example, where we use the TOP keyword in the DELETE statement.

For example:

DELETE TOP(3)
FROM emp
WHERE firstName='Murphy';

This SQL Server DELETE example would delete the first 3 records from the emp table where the firstName is ‘Murphy’. If there are other records in the emp table that have a firstName value of ‘Murphy’, they will be unaffected by the DELETE statement.

Admin has written 171 articles