DELETE Statement – Deleting rows

The DELETE statement deletes rows from a table. This statement has two different forms:

DELETE FROM table_name
	[WHERE predicate];
	
DELETE table_name
	FROM table_name [,...n]
	[WHERE condition];Code language: CSS (css)

All rows that satisfy the condition in the WHERE clause will be deleted. Explicitly naming columns within the DELETE statement is not necessary (or allowed), because the DELETE statement operates on rows and not on columns.

Example 7.16 shows an example of the first form of the DELETE statement.

Delete all managers in the works_on table:

USE sample;
DELETE FROM works_on
	WHERE job = 'Manager';Code language: PHP (php)

The WHERE clause in the DELETE statement can contain an inner query, as shown in Example 7.17.

Mrs. Moser is on leave. Delete all rows in the database concerning her:

USE sample;
DELETE FROM works_on
	WHERE emp_no IN
	(SELECT emp_no
		FROM employee
		WHERE emp_lname = 'Moser');

DELETE FROM employee
	WHERE emp_lname = 'Moser';Code language: PHP (php)

Example 7.17 can also be performed using the FROM clause, as Example 7.18 shows. This clause has the same semantics as the FROM clause in the UPDATE statement.

USE sample;
DELETE works_on
	FROM works_on, employee
	WHERE works_on.emp_no = employee.emp_no
	AND emp_lname = 'Moser';

DELETE FROM employee
	WHERE emp_lname = 'Moser';Code language: PHP (php)

The use of the WHERE clause in the DELETE statement is optional. If the WHERE clause is omitted, all rows of a table will be deleted, as shown in Example 7.19.

USE sample;
DELETE FROM works_on;Code language: PHP (php)