 |
|
Oracle Tips by Burleson |
UPDATE
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;
AUTHOR_KEY
AUTHOR_CONTRACT_NBR
----------- -------------------
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
INSERT
Oracle UPDATE performance
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
