UPDATE [ONLY] { table or UPDATE expression} { alias }
table SET column = {
expression | DEFAULT } [, ...]
[ FROM fromlist ]
[WHERE condition]
[RETURNING { expression} INTO {data_items}];UPDATE changes the values of the specified columns in all rows that satisfy the condition as specified in UPDATE.
In order to view the changes you have made what you mostly do is, update the table, and then do a select to see the updated values. In EnterpriseDB you can immediately see the results of an UPDATE without doing SELECT by using the RETURNING CLAUSE feature. The general format for the UPDATE statement when using a RETURING clause for a single table UPDATE is has been described above.
This is a valid table, materialized view, updateable single-table view, or an expression based on one of the above.
The name of a column in table. The column name can be qualified with a subfield name or array subscript, if needed.
An expression to assign to the column. The expression may use the old values of this and other columns in the table.
Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).
A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement; for example, an alias for the table name can be specified.
An expression that returns a value of type boolean. Only rows for which this expression returns true will be updated.
A valid set of variables in which to load the values returned by the expressions will be updated.
On successful completion, the result of an UPDATE RETURNING can be either:
A single row
Multiple rows
Single set aggregates are used when returning a single row, whereas multiple set aggregates are used when returning multiple rows. The following examples demonstrate the usage of each of these:
UPDATE RETURNING with a Single-set aggregate:
CREATE OR REPLACE PROCEDURE SingleRowUpdateReturn
IS
empName VARCHAR2(50);
empSalary NUMBER(7,2);
BEGIN
UPDATE emp
SET sal = sal + 1000
WHERE empno = 7499
RETURNING ename, sal
INTO empName, empSalary;
dbms_output.put_line('Name of Employee: ' || empName);
dbms_output.put_line('New Salary: ' || empSalary);
END;
UPDATE RETURNING with a Multiple-set aggregate.
Let's consider when an UPDATE statement affects more than one row. In order to see the affected rows, we can use arrays to hold the values. We can iterate through the array to see the values.
CREATE OR REPLACE PROCEDURE MultipleRowUpdateReturn
IS
TYPE emp_table IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER;
v_empRecs emp_table;
BEGIN
UPDATE emp
SET sal = sal * 1.1
WHERE job = 'CLERK'
RETURNING empno, ename, job, mgr, hiredate, sal, comm, deptno
BULK COLLECT INTO v_empRecs;
dbms_output.put_line('Rows Updated: ' || SQL%ROWCOUNT);
FOR I IN v_empRecs.FIRST .. v_empRecs.LAST
LOOP
dbms_output.put_line('Employee Name: ' || v_empRecs(i).ename);
dbms_output.put_line('New Salary: ' || v_empRecs(i).sal);
END LOOP;
END;