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.
Bạn đang xem 5. - OCA OCP ORACLE DATABASE 11G A LL IN ONE EXAM GUIDE P37 DOC