Set value based on a select statement : Update « Insert Update Delete « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Insert Update Delete
3. Update

SQL> create table dept
  2  ( deptno        int primary key,
  3    sum_of_salary number
  4  );

Table created.

SQL>
SQL> create table emp
  2  ( empno      int primary key,
  3    deptno     references dept,
  4    salary     number
  5  );

Table created.

SQL>
SQL> insert into dept ( deptno ) values ( 1 );

1 row created.

SQL>
SQL> insert into dept ( deptno ) values ( 2 );

1 row created.

SQL> insert into emp ( empno, deptno, salary )
  2  values ( 100, 1, 55 );

1 row created.

SQL>
SQL> insert into emp ( empno, deptno, salary )
  2  values ( 101, 1, 50 );

1 row created.

SQL>
SQL>
SQL> update dept
  2     set sum_of_salary =
  3     ( select sum(salary)
  4         from emp
  5            where emp.deptno = dept.deptno )
  6   where dept.deptno = 1;

1 row updated.

SQL>
SQL> commit;

Commit complete.

SQL> select * from emp;

     EMPNO     DEPTNO     SALARY
---------- ---------- ----------
       100          1         55
       101                    50


SQL> select * from dept;

    DEPTNO SUM_OF_SALARY
---------- -------------
         1           105

         2


SQL>
SQL>
SQL>
SQL> drop table dept cascade constraint;

Table dropped.

SQL>
SQL> drop table emp cascade constraint;

Table dropped.