Home » SQL Tutorial » SQL DELETE

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;

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;

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;

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.

SQL DELETE statement - employees and territories tables

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;
DELETE FROM employeeterritories
WHERE employeeID = 3

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.

Databases  #

Was this helpful?