SQL DELETE

Summary: in this tutorial, you will learn how to use SQL DELETE statement to remove one or more rows in a table.

The DELETE statement deletes one or more rows from a table permanently.

Here’s the syntax of the DELETE statement:

DELETE FROM table
WHERE condition;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table from which you want to delete data in the  DELETE FROM clause.
• Second, specify a condition in the WHERE clause filter rows to delete. If you omit the WHERE clause, the DELETE statement will remove all rows in the table.

If you want to remove all rows in a big table, you should use the TRUNCATE TABLE statement which is more efficient than the DELETE statement.

SQL DELETE statement examples

Let’s take a look at some examples of using the DELETE statement.

1) Deleting one row from a table

The following statement deletes the employee with id 3 from the employeestable:

DELETE FROM employees
WHERE employeeID = 3;Code language: SQL (Structured Query Language) (sql)

2) Deleting all rows from a table

To remove all rows in the employeestable, you execute the following query: (not recommended and make a backup before you do this)

DELETE FROM employees;Code language: SQL (Structured Query Language) (sql)

3) Deleting related rows from multiple tables

It becomes more complicated when you want to delete a row in a table that is associated with other rows in another table.

For example, each employee is working in one or more territories and each territory has multiple employees.

The employeeterritories table is used to store relationships between employees and territories.

When you remove a row in the employeestable, you must also remove the related rows in the employeeterritories table. To do so, you have to execute two DELETEstatements as follows:

DELETE FROM employees
WHERE employeeID = 3;Code language: SQL (Structured Query Language) (sql)

DELETE FROM employeeterritories
WHERE employeeID = 3Code language: SQL (Structured Query Language) (sql)

Typically, database management systems allow you to create a foreign key constraint so that if you delete a row in a table, the corresponding rows in the related table are also removed automatically.

This ensures the integrity of the data. In this case, you have to execute the first DELETE statement to delete rows in two tables.

If the database management system does not support the foreign key constraint, you have to execute both DELETE statements in a single transaction to ensure that the statements execute in all-or-nothing mode.

Summary

• Use SQL DELETE statement to delete one or more rows from a table.