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;