Oracle update usage

Note: note the conditions when performing the update operation. If the where clause is left blank, the full table is updated. If the table is not updated, null values are assigned.

Update a single table: update tablename set col1 = value where col2 = 'value2 ';

Update a set. col1 = (select B. col1 from B where. col2 = B. col2) where exists (select * from B where. col2 = B. col2); -- exists conditions must exist, otherwise the update is incorrect.

Multi-Field update: update a set (. col1,. col2) = (select B. col1, B. col2 from B where B. col3 =. col3 and B. col4 = value2) where exists (select 1 from B where B. col3 =. col3 and B. col4 = value2); -- sameA condition is required. If no where clause is specified, full table update is performed. If the conditions are not met, null is returned..

Update a set a. col1 = 100 update col1, a specific column of all rows, to a specific value.
Update a set a. col1 = 100 where a. col2 <10 update the values of col1 columns of rows that meet the col2 condition to specific values.
Update a set a. col1 = a. col1 + a. col2 where a. col2 <10 simple computing update in the same table
Update a set a. col1 = (select B. col1 from B where a. col2 = B. col2)
Where exists (select * from B where a. col2 = B. col2) cascade update will update a. col1 of the rows that meet a. col2 = B. col2 to the corresponding
The value of B. col1. If and only when a = B, the where condition can be removed. This update can also be understood as follows:
Update a set a. col1 = (select B. col1 from B where a. col2 = B. col2) indicates that all rows in a meet a. col2 = B. col2
,If the condition is not met, it is also updated, but the corresponding value cannot be found. Only null values can be assigned.If a. col1 is not allowed to be null at this time, a null value insertion error will be reported.
So onlyAdd the where ConditionTo survive the data of a. col2 <> B. col2 in a and is not updated to null ).

The inline view update method updates a temporary view.

Update (select. state as state_a, B. state as state_ B from a, B where. col1 = B. col1 and. col2 = value) set state_a = state_ B; -- col1 is the primary key of Table B.

Create a view by associating two tables in brackets, and set the updated field in set. Intuitive speed, but B's primary key must be in the where condition, and is associated with the updated table with "=", otherwise the error: ora-01779: the columns corresponding to the non-key-value saving table cannot be modified.

Merge into for multi-Table update: merge table1 into table2 on condition when matched then update table1 set col1 = value1, col2 = value2 when not matched then insert (col_list) values (value_list );

Quick cursor update method:

Begin

For cr in (query statement) loop -- loop

-- Update the statement according to the query result set)

End loop;

End;

Oracle supports quick cursors. You do not need to define the cursors to write them directly to the for loop to facilitate batch data update. With the rowid physical field, you can quickly locate the record to be updated.

Solution	Suggestions
Standard update	It is better to use this solution for single-table updates or simpler statements.
Inline view update Method	Two tables associated with the updated table are associated with the primary key of the joined table. This solution is better.
Meger into update Method	Two tables associated with the updated table are not associated with the primary key of the joined table. This solution is better.
Quick cursor update Method	Multi-Table Association and complex logic, this solution is better

This article is from the "Running antelope" blog, please be sure to keep this source http://heshw.blog.51cto.com/5891747/1293594