The DELETE statement

The delete statement facilitates the removal of data from the database. Like the SELECT statement, the DELETE statement contains a WHERE clause that specifies the conditions used to identify rows to be deleted. If we neglect to add a WHERE clause to our DELETE statement, all rows will be deleted from the target table.

Syntax:

DELETE <Column1, Column2,..>

 FROM table_name

Where <one or more condition apply>

The following statement will delete all employees with the last name of ‘Smith’ from the employee table:

DELETE FROM employee

WHERE lname = 'Smith';

In some cases, the values needed for one or more of the conditions in your WHERE clause exist in another table. For example, your company may decide to outsource its accounting functions, thereby necessitating the removal of all Accounting personnel from the employee table:

DELETE FROM employee

WHERE dept_id = (SELECT dept_id  FROM department  WHERE name = 'Accounting');