SQL DELETE statement allows you to delete one or more records in a database table. The syntax of SQL DELETE statement is as follows:
DELETE FROM table_name
WHERE conditions
If you omit the WHERE clause the SQL DELETE statement, it will delete all records in the database table. It is very time consuming and less efficient to use SQL DELETE statement to do so especially with the table with a big data. If you want to delete all data in a database table, SQL provide you TRUNCATE statement which is more efficiently to delete the whole table.
Let’s take a look at couples of examples using SQL DELETE statement.
If you want to remove employee number 3 just execute the following query:
DELETE FROM employees
WHERE employeeID = 3
If the record which has employeeID with 3 exist, it will be deleted otherwise nothing happens.
To delete all employees in the table employees (not recommended, and make a backup before you do this) you just execute the following query:
DELETE FROM employees
SQL DELETE statement become complicated when you delete a record in a table which has relationship and link together by a foreign key such as employees and employeeterritories. If you want to delete an employee you have to delete a record which has employeeid in employeeterritories table also. So you have to execute two DELETE statements as follows:
DELETE FROM employees
WHERE employeeID = 3;
DELETE FROM employeeterritories
WHERE employeeID = 3
Almost RDBMS allows you to create a constraint called referential integrity between two linked tables so one record in a table deleted other records in the linked table if exist are deleted also. Therefore in this case you just only have to execute the first DELETE query make the data integrity.
In this tutorial, you’ve learnt how to use SQL DELETE statement to delete one or more records in a database table. You’ve also learnt about referential integrity constraints between tables to allow you to delete records in linked table automatically by deleting record in a database table.