Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training


  Oracle Tips by Burleson


The Oracle UPDATE statement locates one or more rows (or all the rows) in a table and sets one or more columns to the specified values.  As with the INSERT statement, the values must either match the columns data type or one that the database can implicitly convert.   The basic format is: 

update <table name> set (<col1 = val1, col2 = val2,col3 = val3,…)   where <expression identifying rows to change>; 

Let’s go back to the AUTHOR table and look at some examples of Oracle updates.  As with the INSERT section, I rolled back the changes to the original table.

To set each author contract number to zero, simply leave off the WHERE clause.  If you do not specify a row, then all rows are updated when Oracle update is used.

SQL> select author_key, author_contract_nbr from author; 

----------- -------------------
A101                       5601
A102                       5602
A103                       5603
A104                       6602
A105                       7896
A106                       6547
A107                       3452
A108                       7954
A109                          1
A110                       2853 

10 rows selected. 

SQL> update author set author_contract_nbr = 0000;

10 rows updated.

The  command returns the table to the state it was in before the Oracle updates. 

To change the contract number for author A109 to 999, use the statement below.  Notice that the WHERE clause identifies which row will be updated with Oracle UPDATE.

SQL> update
  2    author
  3  set author_contract_nbr = 999
  4  where
  5    author_key = 'A109';

1 row updated.

I can also set multiple columns in one Oracle update.  A comma separates each column to be changed.  All of the columns identified will be changed for all the rows specified in the WHERE clause.

SQL> update
  2    author
  3  set author_contract_nbr = 8888,
  4      author_zip          = 32076
  5  where
  6    author_state = 'MO';

3 rows updated.

One of the powerful features of the Oracle update statement is the ability to update rows using a query.

update <table name> set (col1, col2, col3,…) = (<query>)
    where <expression>;

The query must have a value in the select clause for each column in the column list.  If the where clause is not used all rows are updated when the Oracle update is executed.

SQL> update
  2    sales
  3  set (order_date, quantity) = (select
  4                                  SYSDATE,
  5                                  avg(quantity)
  6                                from sales
  7                                where book_key = 'B102'
  8                                group by book_key, SYSDATE)
  9  where book_key = 'B102';

11 rows updated.

The query in the Oracle UPDATE statement defines the values to update the columns.  I still need the WHERE clause in the Oracle UPDATE statement to define which rows were to be updated.

So far, we can insert new rows, change rows already in the table, and now we need a way to remove rows that we no longer want in the table.

The above text is an excerpt from:

Easy Oracle SQL
Get Started Fast Writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

by John Garmany

Related links:


Oracle UPDATE performance

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel


email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.