Delete: Let's Downsize The Company

Syntax

DELETE FROM <table_name>
WHERE <condition>;

Let's Get Rid Of Anyone Whose Name Starts With "B"


SQL> select ename,job,sal from emp where ename like 'B%';

ENAME	   JOB		    SAL
---------- --------- ----------
BLAKE	   MANAGER	   3135

Look before you DELETE!

Let's give BLAKE the RIP (Retirement Incentive Plan), but before we do, we have to get rid of his department and reassign his employees to another manager.

Who are BLAKE's EMPLOYEEs?

SQL> select ename from emp where mgr = (select empno from emp where ename = 'BLAKE');

ENAME
----------
ALLEN
WARD
MARTIN
TURNER
JAMES

Who are the other Managers?

SQL> select ename from emp where job = 'MANAGER' ;

ENAME
----------
JONES
BLAKE
CLARK

JONES is about to move up.

SQL> update emp set mgr = (select empno from emp where ename = 'JONES') 
     where mgr = (select empno from emp where ename = 'BLAKE');

5 rows updated.

Thanks for taking the incentive package BLAKE

SQL> delete emp where ename = 'BLAKE';

1 row deleted.

SQL> commit;

Commit complete.


Bob Dugan bdugan@stonehill.edu