Shulou(Shulou.com)06/01 Report--

1. The syntax and principle of update sentence

1. Grammar

Single table: UPDATE table name SET column name = new value WHERE column name = a value

Such as: update t_join_situation set join_state='1'whereyear='2011'

The join_state field for data updated with "2011" is "1". If the updated field is indexed, the index will be rebuilt during the update, and the update efficiency will be slow.

Associate multiple tables and update the field values of one table to the fields in another table:

Update table a set a. Field 1 = (select b. Field 1 from table b where a. Field 2roomb. Field 2) where exists (select 1 from table b where a. Field 2roomb. Field 2)

The update statement of oracle is not as easy to write as MSSQL. Even if it is written, it may be reported when it is executed.

This is because the subquery where the set finds multiple rows of data values, and oracle specifies that the data is updated one-to-one, so it prompts an error. In order to solve this problem, we must ensure that the values found correspond one to one.

two。 Principle

The principle of the Update statement is that after finding the data according to the where condition, if there is a subquery in the set, then execute the subquery to check out the value to assign to the updated field, and perform the update.

For example, update table a set a. Field 1 = (select b. Field 1 from table b where a. Field 2roomb. Field 2) where exists (select 1 from table b where a. Field 2roomb. Field 2). Look up all the data in table an and cycle through each piece of data to verify that it conforms to exists (select 1 from table b where a. Field 2roomb. Field 2) condition, if yes, execute (select b. Field 1 from table b where a. Field 2roomb. Field 2) query, find the corresponding value update a. In field 1. You must have exists (select 1 from table b where a.) when updating the associated table. Field 2roomb. Field 2) such a condition, otherwise update field 1 of other data in table a to a null value.

II. Various solutions to improve the efficiency of oracle updates

1. Standard update syntax

When the table you need to update is a single table or the updated field does not need to be associated with other tables, then finally select the standard update statement, which has the fastest speed, the best stability, and returns the number of influence entries. If the fields in the where condition are indexed, the update is more efficient. However, update is very inefficient when you need to update fields in an associated table.

2. Inline view updating method

The inline view update method is to update a temporarily created view. Such as: update (select a.join_state asjoin_state_ a. B. Jointed state asjoin_state_b)

From t_join_situation a, t_people_info b where a.people_number=b.people_number

And a.year='2011'and a.city_number='M00000'and a. Townships numbered M51000') set join_state_a=join_state_b

In parentheses, a view is created by associating two tables, and the updated fields are set in set. This solution is more intuitive and faster than writing. However, the primary key of Table B must be in the where condition, and the updated table must be associated with "=", otherwise an error will be reported:

3.merge updating method

Merge is a statement specific to oracle. The syntax is as follows:

MERGE INTO table_name alias1

USING (table | view | sub_query) alias2

ON (join condition)

WHEN MATCHED THEN

UPDATE table_name

SET col1 = col_val1

Col2 = col2_val

WHEN NOT MATCHED THEN

INSERT (column_list) VALUES (column_values)

Its principle is that the data Select out of alias2, each of which is compared with alias1 by ON (join condition), if there is a match, the update operation (Update) is carried out, and if there is no match, the insert operation (Insert) is carried out. Executing merge does not return the number of rows affected. The writing of Merge statements is cumbersome and can only be associated with two tables at most. Complex statements using merge update method will be inadequate and inefficient.

4. Fast Vernier updating method

Grammar such as:

Begin

For cr in (query statement) loop-loop

Update statement (based on the result set of the query)

Endloop;-end the loop

End

Oracle supports fast cursors and writes cursors directly into for loops without definition, which makes it convenient for us to update data in batches. Coupled with oracle's rowid physical field (oracle defaults to the rowid field for each table and is the only index), you can quickly navigate to the record to be updated.

Examples are as follows:

Begin

For cr in (select a. Rowid.b. Jointed state from t_join_situation a people in person info b

Where a.people_number=b.people_number

And a.year='2011'and a.city_number='M00000'and a. Townships numbered M51000') loop

Update t_join_situation set join_state=cr.join_state where

Rowid = cr.rowid

Endloop

End

There are many advantages of using fast cursors, which can support complex query statements and update accurately. No matter how large the data is, the update efficiency is still high, but the number of rows is not returned after execution.

Conclusion

Standard update syntax

It is better to use this scheme for single table updates or simpler statements.

Inline view updating method

If two tables are associated and the updated table is associated through the primary key of the associated table, this scheme is better.

Merge updating method

The two tables are associated and the updated tables are not associated through the primary key of the associated table, so it is better to adopt this scheme.

Fast Vernier updating method

If multiple tables are associated and the logic is complex, this scheme is better.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.