1.9. Deletions

Suppose that department 20 closes down for some reason and you are no longer interested in the employees belonging to department 20. Then you can do the following to delete those rows from the table. Deletions are performed using the DELETE command:

DELETE FROM emp WHERE deptno = 20;

All employee records belonging to department 20 are removed.

SELECT * FROM emp;

 empno | ename  |    job    | mgr  | hiredate  |   sal   |  comm   | deptno
-------+--------+-----------+------+-----------+---------+---------+--------
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 | 2450.00 |         |     10
  7839 | KING   | PRESIDENT |      | 17-NOV-81 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 | 1500.00 |    0.00 |     30
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 |  950.00 |         |     30
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 | 1300.00 |         |     10
(9 rows)

One should be wary of statements of the form

DELETE FROM tablename;

Without a qualifying WHERE clause, DELETE will remove all rows from the given table, leaving it empty. The system will not request confirmation before doing this!