Modify the values stored in a table.
Syntax:
UPDATE [hint] [schema.]table [@dblink] [alias] SET col_expr(s) [WHERE condition] [ RETURNING (expr,…) INTO (data_item,…) ] UPDATE [hint] [schema.]table [[SUB]PARTITION (partition)] [alias] SET col_expr(s) [WHERE condition] [ RETURNING (expr,…) INTO (data_item,…) ] UPDATE [hint] [schema.]view [@dblink] [alias] SET col_expr(s) [WHERE condition] [ RETURNING (expr,…) INTO (data_item,…) ] UPDATE [hint] [schema.]materialized_view [@dblink] [alias] SET col_expr(s) [WHERE condition] [ RETURNING (expr,…) INTO (data_item,…) ] UPDATE [hint] (subquery) WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} SET col_expr(s) [WHERE condition] [ RETURNING (expr,…) INTO (data_item,…) ] UPDATE [hint] TABLE (table_collection_expression) [(+)] SET col_expr(s) [WHERE condition] [ RETURNING (expr,…) INTO (data_item,…) ] col_expr: column = expression column = (subquery) column = DEFAULT (column, column,…) = (subquery) VALUE (table_alias) = expression VALUE (table_alias) = (subquery) To update multiple columns, separate col_expr with commas.
The terms "snapshot" and "materialized view" are synonymous.
table_collection_expression can be a subquery, a column, a function, or a collection constructor, it must return a collection value (that is, a value whose type is nested table or varray). This allows you to update rows in one table based on rows from another table.
The syntax above can also be modified to UPDATE [hint] ONLY (expression)
Use this syntax if the view in the update clause belongs to a hierarchy and you do not want to update rows from any of its subviews.
Examples
Update every row in the table SALES:
UPDATE Sales SET Supplier_ID=123;
Update multiple rows in the table SALES:
UPDATE Sales
SET Supplier_ID=123
WHERE
Supplier_ID=25;
Update two columns in the table JOURNALS, use SELECT… FROM DUAL to present the two numbers that will be inserted as a subquery, note that two sides of the SET statement must have the same number of columns:
UPDATE journals
SET (jo_money,jo_account_ref) = (select 64.5, 1467 from dual)
WHERE jo_journal_pk ='RS512';
Update two columns in the table JOURNALS, insert a numeric and a date value:
UPDATE journals
SET (jo_money,jo_date) = (select 64.5, to_date('2011-Dec-30', 'YYYY'-Mon-DD) from dual)
WHERE jo_journal_pk ='RS512';
Update multiple rows in a subquery (joining two tables):
UPDATE ( Select prize
from Competitions c
inner join Gamblers g on c.comp_id = g.comp_id
where g.gambler_id = 456 ) p
SET p.prize = 10000
“Even God cannot change the past” ~ Agathon
Related Oracle Commands:
DELETE - Delete rows
EXPLAIN PLAN
INSERT - Insert rows
MERGE - Select rows for update or insert into a table.
SELECT
TRUNCATE - Empty table
ALTER TRIGGER - Disable a trigger to perform bulk updates without the trigger firing.
Related Views:
DBA_SNAPSHOTS ALL_SNAPSHOTS USER_SNAPSHOTS DBA_ALL_TABLES ALL_ALL_TABLES USER_ALL_TABLES DBA_TABLES ALL_TABLES USER_TABLES TAB DBA_VIEWS ALL_VIEWS USER_VIEWS DICTIONARY DICT_COLUMNS