Tuesday, 15 April 2014

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

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;

--------- ---------- --------- ---------- --------- ---------- --------- --------- --------- --------- --------- --------- 
     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.
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.

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...