Update and Delete in Correlated Sub query

Correlated sub queries as you all know are used for row-by-row processing. Here each sub query is executed once for every row of the outer query. The oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. 

The general for of a correlated subquery is:

select column1, column2, . . . . . . .

from table1 outer

where [column1] operator (select column1, column2,

from table2 where expr1= outer.expr2);

To read more about Sub queries please refer our previous post

http://dwhlaureate.blogspot.in/2012/08/sub-queries-in-oracle.html

Now will see how to use correlated update and delete statements 

1.Correlated Sub query Update Examples

The general form:

Update table1 alias1 set column = (select expression from table2 aliase2 where alias1.column=alias2.column);

In the case of correlated update statement, we can use a correlated subquery to update rows in one table based on rows from another table.

Ex:  Correlated update

·         Add a column to Employee table to store the department name.

·         Populate the table by using a correlated update.

Alter table Employee add(deptname varchar2(12));

Table altered.

Update Employee e set deptname=(select dname from dept d where e.deptno=d.deptno);

14 rows updated.

 select * from Employee;

EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO DEPTNAME

--------- ---------- --------- ---------- --------- ---------- --------- --------- --------- --------- --------- --------- 

     7369 SMITH      CLERK           7902 17-DEC-80        800                       20             Research

     7499 ALLEN      SALESMAN   7698 20-FEB-81      1600         300           30            Sales

     7521 WARD       SALESMAN   7698 22-FEB-81      1250         500           30             Sales

     7566 JONES      MANAGER    7839 02-APR-81       2975                       20             Research

     7654 MARTIN  SALESMAN    7698 28-SEP-81      1250          1400        30             Sales

     7698 BLAKE      MANAGER     7839 01-MAY-81   2850                        30             Sales

     7782 CLARK      MANAGER     7839 09-JUN-81     2450                       10             Accounting

     7788 SCOTT      ANALYST       7566 09-DEC-82      3000                      20             Research

     7839 KING       PRESIDENT               17-NOV-81     5000                    10             Accounting

     7844 TURNER  SALESMAN    7698 08-SEP-81        1500         0            30             Sales

     7876 ADAMS      CLERK           7788 12-JAN-83      1100                      20             Research

     7900 JAMES      CLERK            7698 03-DEC-81        950                      30             Sales

     7902 FORD       ANALYST        7566 03-DEC-81      3000                     20             Research

     7934 MILLER     CLERK           7782 23-JAN-82      1300                       10             Accounting

14 rows selected.

If we want to use the ‘exists’ operator in the where clause:

SQL> update emp e set deptname='Revenue' where exists (select dname from dept d where e.deptno=d.deptno);

14 rows updated.

2.Correlated Sub query Delete Examples

 The general form:

delete from table1 alias1 where column operator (select expression from table2 alias2 where  alias1.column=alias2.column);

We can use a correlated subquery to delete rows in one table based on the rows from another table.

Example:

SQL> delete from emp e where deptno = (select deptno from dept where deptno=e.deptno);

14 rows deleted.

SQL> select * from emp;

no rows selected

SQL> rollback;

Rollback complete.