Update multi-row data (oracle)

Update multi-row data (oracle)
Note: The notes summarize several update Methods encountered at work and the applicability of various methods. 1. single Table update solution: use the standard update syntax to perform a stable and efficient update tableset (column1, column2 ,...) = value1, value2 ,...; 2. example of Multi-table join update: update all the rows in the gkfq_rec table with the same slid as the fi_inst in the oa2_ftask table. The blzt field value is equal to the ft_lstate in the oa2_ftask table.

create table gkfq_rec (slid char(12) parimary key,blzt varchar2(50),wjbt varchar2(100) not null,........);create table oa2_ftask (fi_inst char(12) parimary key,fi_state int not null,ft_lstate int not null,...);

 

Method description	Applicability	Operation Efficiency
Traditional Solution	Applicable in general	The update efficiency of a single table is high and stable, and the timeliness of multiple tables is slow.
Inline view update Method	The joined field is the primary key.	Fast
Merge update Method	The joined field is not a primary key and is applicable to join two tables.	Non-primary key associated table update, fast
Quick cursor update Method	Complex logic	Complex logic has a high validity rate

(1) traditional solutions (the speed may be the slowest)

Update gkfq_rec a set blzt = (select B. ft_lstate from oa2_ftask B where. slid = B. fi_inst) where exists (select 1 from oa2_ftask B where. slid = B. fi_inst); // when the subquery returns values of multiple rows, the where exists condition is used for Row-by-row filtering to implement one-to-one matching of set unique values.

 

(2) inline view update method (quickly associating primary key fields): update a temporary view. The primary key field of Table B must be in the where condition and associated with the updated table with the = number; otherwise, an error may be reported: ORA-01779: The columns corresponding to the non-key-value save table cannot be modified. This error may also occur when the primary key fields in Table B are combined into multiple columns. Update (select. blzt as blzt, B. ft_lstate as ft_lstatefrom gkfq_rec a, oa2_ftask B where. slid = B. fi_inst) set blzt = ft_lstate; (3) merge update method (faster when correlated fields are not primary keys) Syntax:

MERGE INTO table_name alias 1USING (table|view|sub_query) alias 2ON (join condition)WHEN MATCHED THENUPDATE SET col1=col_val1,    col2=col_val2WHEN NOT MATCHED THENINSERT (column_list) VALUES (column_values);

 

Solution: for the data selected in alias2, each entry is compared with alias1 in ON (join condition). If it matches, the update operation is performed. If it does not match, the insert operation is executed. Merge does not return the number of affected rows, and can only be associated with two tables at most. This applies to fields where the connection condition is not a primary key.

merge into gkfq_rec ausing oa2_ftask bon (a.slid=b.fi_inst)when matched thenupdate set a.blzt=b.ft_lstate;

 

(4) Quick cursor update (efficient in complex logic) Syntax: beginfor cr in (query statement) loop -- loop update table_name set... -- Update the statement (based on the query result set) end loop; -- end loop end; solution: Use a quick cursor with the built-in ROWID Physical Field unique to oracle and do not need to be defined, write the cursor directly to the for loop to quickly locate and execute updates. It supports query statements with complex logic and is updated accurately, regardless of how much data is updated. The number of affected rows is not returned after execution.

beginfor aa in (select a.rowid as rowid,b.ft_lstate as ft_lstate from gkfq_rec a,oa2_ftask bwhere a.slid=b.fi_inst ) loopupdate gkfq_rec set blzt=aa.ft_lstatewhere rowid=aa.rowid;end loop;end;

 

Update multi-row data (oracle)