Oracle UPDATE Statement
The Oracle UPDATE statement changes the values of specified columns in one or more rows in a table or view.
For you to update values in a table, the table must be in your own schema or you must have UPDATE privilege on the table.
For you to update values in the object (table, base table of a view)
You must have UPDATE AND SELECT privilege on the table/view, and
Whoever owns the schema containing the view must have UPDATE privilege on the base table.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.
The object is on a remote database or
The SQL92_SECURITY initialization parameter is set to TRUE and the UPDATE operation references table columns (such as the columns in a where_clause).
The syntax the UPDATE Oracle statement is:
UPDATE table
SET column = expression
WHERE predicates;
More complex example
You may wish to update records in one table based on values in another table. Since you can't list more than one table in the UPDATE statement, you can use the EXISTS clause.
For example:
UPDATE table
SET column = (SELECT expression FROM table2 WHERE table.colum=table2.column)
WHERE predicates;
You ca use EXIST function too.
UPDATE table
SET column = (SELECT expression FROM table2 WHERE table.colum=table2.column)
WHERE EXIST (SELECT expression FROM table2 WHERE table.colum=table2.column);
Learn more about the EXISTS condition.
|
|
|
Using the RETURNING Clause During UPDATE: Example
The following example returns values from the updated row and stores the result in PL/SQL variables bnd1, bnd2, bnd3:
UPDATE employees
SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140
WHERE last_name = 'Jones'
RETURNING salary*0.25, last_name, department_id
INTO :bnd1, :bnd2, :bnd3;
See also insert into Oracle
|
|
|
|
|
|
|
|