Oracle data update-CodePudding

Have a, b two tables, a table with fields TDZH, WJLJ, id, b table has TDZH, WJLJ, jsydid
Will now update id of a table to table b jsydid, judgment conditions are two tables WJLJ and TDZH, but can WJLJ need to capture part of the b table with a table of WJLJ equal,
That statement now how to write better? Below is the statement I wrote, but the efficiency is too low

 DECLARE 
CURSOR csr_data IS 
SELECT * 
The FROM gltpid; 
The BEGIN 
FOR a csr_data IN 
LOOP 
The UPDATE dzdagxb b 
SET 
B.j sydid=Anderson d 
WHERE b.w JLJ=substr (b.w JLJ, instr (b.w JLJ, '/', 1, 1), instr (b.w JLJ, '/', 1, 5) + instr (b.w JLJ, '/', 1, 1), 2) 
And 
A. d. ah=b.t DZH. 
END LOOP; 
END;

CodePudding user response:

Commit more!

CodePudding user response:

add a table a data corresponding to b more than table data, so a table an id will give more than b table data is replaced jsydid

CodePudding user response:

1. Consider the Merge into.

2. B.w JLJ=substr (b.w JLJ, instr (b.w JLJ, '/', 1, 1), instr (b.w JLJ, '/', 1, 5) + instr (b.w JLJ, '/', 1, 1) - (2)
How is b field, consider how to optimize the function, can see use regular expressions to make it simple point

3. Try the bulk collect more data. Or commit by number of article.

CodePudding user response:

The

reference 3 floor u012557814 response:

1. Consider the Merge into the

2. B.w JLJ=substr (b.w JLJ, instr (b.w JLJ, '/', 1, 1), instr (b.w JLJ, '/', 1, 5) + instr (b.w JLJ, '/', 1, 1) - (2)
How is b field, consider how to optimize the function, can see use regular expressions to make it simple point

3. Try the bulk collect more data. Or according to the article number of the commit.

Behind the end loop commit?

CodePudding user response:

Cursor to

 - why? 
- the idea is to use the update join replace cursor 
The update b 
The set b.j sydid=
(select Anderson d 
From a 
Where a.t DZH=b.t DZH 
And instr (b.w JLJ, aleem walji JLJ) - replaced aleem walji JLJ=substr (b.w JLJ, instr (b.w JLJ, '/', 1, 1), instr (b.w JLJ, '/', 1, 5) + instr (b.w JLJ, '/', 1, 1), 2) 
And 
) 
Where the exists (select 1 
From a 
Where a.t DZH=b.t DZH 
And instr (b.w JLJ, aleem walji JLJ) 
);

CodePudding user response:

 there is an error in the code above, use the 
The update b 
The set b.j sydid=
(select Anderson d 
From a 
Where a.t DZH=b.t DZH 
And instr (b.w JLJ, aleem walji JLJ)=1 - replace the aleem walji JLJ=substr (b.w JLJ, instr (b.w JLJ, '/', 1, 1), instr (b.w JLJ, '/', 1, 5) + instr (b.w JLJ, '/', 1, 1), 2) 
And 
) 
Where the exists (select 1 
From a 
Where a.t DZH=b.t DZH 
And instr (b.w JLJ, aleem walji JLJ)=1 
);

CodePudding user response:

I take myself, the above still has a problem, no environment debugging, forgive me!

 update bThe set b.j sydid=
(select Anderson d 
From a 
Where a.t DZH=b.t DZH 
And instr (b.w JLJ, aleem walji JLJ)=1) 
Where the exists (select 1 
From a 
Where a.t DZH=b.t DZH 
And instr (b.w JLJ, aleem walji JLJ)=1);

CodePudding user response:

Low efficiency because of your statement is useless to the index, large amount of data can also be partial to submit