COMMIT THE CHANGES MADE

5. Commit the changes made:

commit;

DELETE

Previously inserted rows can be removed from a table with the DELETE command.

The command will remove one row or a set of rows from the table, depending on a

WHERE clause. If there is no WHERE clause, every row in the table will be removed

(which can be a little disconcerting if you left out the WHERE clause by mistake).

TIP There are no “warning” prompts for any SQL commands. If you instruct

the database to delete a million rows, it will do so. Immediately. There is none

of that “Are you sure?” business that some environments offer.

A deletion is all or nothing. It is not possible to nominate columns. When rows

are inserted, you can choose which columns to populate. When rows are updated, you

can choose which columns to update. But a deletion applies to the whole row—the

only choice is which rows in which table. This makes the DELETE command syntactically

simpler than the other DML commands. The syntax is as follows:

DELETE FROM table [WHERE condition];

This is the simplest of the DML commands, particularly if the condition is omitted.

In that case, every row in the table will be removed with no prompt. The only

complication is in the condition. This can be a simple match of a column to a literal:

delete from employees where employee_id=206;delete from employees where last_name like 'S%';delete from employees where department_id=&Which_department;delete from employees where department_id is null;

The first statement identifies a row by primary key. One row only will be

removed—or no row at all, if the value given does not find a match. The second

statement uses a nonequality predicate that could result in the deletion of many

rows: every employee whose surname begins with an uppercase “S.” The third

statement uses an equality predicate but not on the primary key. It prompts for

a department number with a substitution variable, and all employees in that

department will go. The final statement removes all employees who are not

currently assigned to a department.

The condition can also be a subquery:

delete from employees where department_id in(select department_id from departments where location_id in (select location_id from locations where country_id in (select country_id from countries where region_id in (select region_id from regions where region_name='Europe') ) ))

This example uses a subquery for row selection that navigates the HR geographical

tree (with more subqueries) to delete every employee who works for any department

that is based in Europe. The same rule for the number of values returned by the

subquery applies as for an UPDATE command: if the row selection is based on an

equality predicate (as in the preceding example) the subquery must be scalar, but if

it uses IN the subquery can return several rows.

If the DELETE command finds no rows to delete, this is not an error. The

command will return the message “0 rows deleted” rather than an error message

because the statement did complete successfully—it just didn’t find anything to do.