The delete statement

The delete statement of SQL is used to remove data from tables of the database.

The delete SQL statement removes existing data from the tables.

With a single delete query in SQL, you can remove whole data, a single row or multiple rows from the tables.

To remove single or specific rows from the table, the SQL Delete statement is used with the Where clause.

Where clause is used to filter and then delete rows from the table.

Practically, the delete command is used with the Where clause, it is generally least required to remove whole data from tables.

An example of delete statement

This tutorial will show you how to use delete query in SQL to remove whole data, a single row and multiple rows from a single table.

Syntax of Delete query

The general syntax of delete query is:

Delete from table_name

Where col1=val1 OR/AND col2=val2

In the above syntax, delete from is followed by the table name from where you want to remove the data. The where clause in delete SQL contains a condition that specifies which rows to be removed.

You can simply use:

Delete from table_name

OR

Delete * from table_name

However, this command will remove whole data from the table specified in delete query.

In order to use the delete from SQL statement to remove specific data, you have to use the Where clause. For example when you choose to delete an online email address of yours or some other account permanently. As such, email_accounts table may contain all email addresses of the account holders.

For the above example this is how delete query may look like:

As your email should be unique in a table so it will only delete the single row.

Examples of using delete statement

Let us show you examples of how to use delete with our created database and tables. We will use same database, test_db and tbl_employee table to show a few examples of delete statement.

Just to remind this is how our example table is created in test_db:

Now, as we have a table tbl_employee with five columns (emp_id, emp_name, emp_age, emp_salary and joining_date). Also, we have data by using the insert statement. For delete command examples, we have five rows of data in our table.

See Demo Table Online with five rows

Example of delete for single row

Consider that we want to remove a row of employee ‘Mike’ from our tbl_employee table that keeps only current employees data. This is how the Delete query would look to remove a record:

Demo Table before and after delete

Example of removing data with two conditions in delete statement

Consider we have two employees with the same name ‘Mike’. Running the above delete query will remove both records in our tbl_employee table. In order to delete the record of employee ‘Mike’ who joined 2009-01-14, this is how we can remove it by using delete command of SQL.

This is how delete query in SQL would look:

Demo Table before and after SQL delete

In the above delete query, we used the And operator to add another condition inside the Where clause to differentiate between similar records.

The Delete example of removing whole data

As mentioned earlier, if you use delete statement without the where clause, it will delete whole data from the specified table.

Note that, it will only remove data, whereas physical table structure, constraints still exists.

To remove whole data from table, the delete statement for tbl_employee will be:

Demo Table before and after delete

Also see SQL TRUNCATE TABLE