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