UPDATE

UPDATE:

Update is used to modify the existing values in Table or in the base table of View.

The Table must be in the Own schema.

UPDATE object privilege should be available.

Syntax:

update <table name> set <column name>=<value>[,<column name2>,...]

[where condition];

EX:

SQL>update emp set deptno=20 where empno='1002';

SQL>update employee set sal=5000 where empno='1003';

SQL>update emp set sal=3000,comm=NULL

            where job='CLERK';

Updating Two Columns with a Sub Query:

SQL>update emp set job=(select job from emp

                                    where empno=1004),

                           sal=(select job from emp

                                    where empno=1005),

            where empno=1002;

SQL>update emp set deptno=(select deptno from emp

                                    where empno=1001)

            where job=(select job from emp where empno=1002);

SQL>update emp set (job,deptno)=(select job,deptno

                                    from dept where empno=1003)

            where empno=1002;

Updating Rows based on Another Table:

use sub queries in UPDATE statements to update rows in a table based on values from another table.

SQL>update emp set deptno=(select deptno from dept

                                    where dname='ACCOUNTING')

            where deptno=(select deptno from dept

                                    where dname=-'SALES');

Appling default Values:

It is used to UPDATE a value in a column with DEFAULT value set in the constraints.

SQL>update emp set sal=DEFAULT where ename='FORD';

SQL>update emp set hiredate=DEFAULT where empno=1003;

Returning Clause:

The returning clause is introduced from 8.0.

It is used to return a value from a aggregate function.

This feature is introduced in grid technology.

The clause can be specified for table and materialized views and for views with a single base table.

Restrictions:

Each express must be a simple expression or a single set aggregate function.

It cannot be specified for multiple table insert.

It cannot be specified upon a view upon which a instead of trigger is defined.

SQL>VAR sumsal number;

SQL>update emp set sal=sal*0.25

                        where deptno=10

                        returning sum(sal) into :sumsal;

SQL>var bsal number;

SQL>var bname varchar;

SQL>update emp set sal=sal+sal*0.25

                        where empno=1004

                        returning ename,sal into :bname,:bsal;

SQL>print :bname :bsal;