The delete query in MySQL

A few main points about MySQL delete from statement:

• The delete statement is used to remove data from the tables of MySQL database.
• If you simply use the delete from statement without the where clause, you can remove whole data from the table.
• If you need to remove selective records based on some criteria then use it with the Where clause.
• If you need to remove whole table data, you may want to use TRUNCATE table command. The TRUNCATE is faster than the delete MySQL statement.
• The delete command scans the whole table and it can be used to return total rows affected.

MySQL delete query syntax

Following is the syntax of using the delete from statement:

Delete from table_name

Where col1=val1 OR/AND col2=val2

As you can see in syntax, the delete from is followed by the name of the table. If you leave it here and execute this statement, it will remove whole table data.

After that, we used the Where clause where you can specify one or more conditions to filter out only specific rows to be deleted.

You can simply use:

Delete from table_name

OR

Delete * from table_name

MySQL delete command examples

For our example to demonstrate the delete from statement, we will use the tbl_employee table. If you have not followed other tutorials where we created and used this table, this is how our example table was created:

The tbl_employee table is created with five columns, as you can see in above query. For demo purpose, we have already entered some data in that table.

Deleting a row example

In this example, we will remove a single row from the tbl_employee table. We will specify the employee name in delete from query’s where clause.

It will search the table and if found, it will remove the record. The query and output are shown below:

Delete row example with AND Operator

While using where clause in the delete statement, you can use it the way as in the other commands of MySQL. For example, you can use multiple criteria in the where clause by using OR, And, Like operators etc.

In the following example, we will use AND operator in the Where clause to match two conditions. If both are evaluated as true then matched records will be removed.

The delete query in MySQL would be:

Delete whole table data example

As mentioned earlier, if you want to remove whole table data you can simply use delete from statement without using the where clause. The delete from statement removes only data while the structure of the table (columns, table constraints etc.) remains in place.

The delete from statement removes only data while the structure of the table (columns, table constraints etc.) remains in place.

See example and output below.