UPDATE Statement

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 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 - You may want to disable triggers when performing some bulk updates.

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


© Copyright SS64.com 1999-2014
Some rights reserved