There are two tables A and B, according to two table id field a2, b2 equal conditions, change the value of the B table b3, fixed to 1, as long as it is equal to 1, the stored procedure how to write, not exposed to write A stored procedure, don't know how to write, data volume has more than 20000, optimization of some of the best!

CodePudding user response:

Actually don't have to write a stored procedure, SQL statements can be done:

 
- 1. The data volume is not big, use the update 
The update b 
The set b.b 3=1 
Where the exists (select null from a where clause a.a 2=b.b 2); 

commit; 
- 2. Large amount of data, use the merge into 
The merge into b using a on b.b=a.a 2 
The when matched then 
The set b.b 3=1; 

commit; 

CodePudding user response:

If you want to a stored procedure can also be:

 
- 1. The data volume is not big, use the update 
The create or replace procedure p_test 
Is 
The begin 
The update b 
The set b.b 3=1 
Where the exists (select null from a where clause a.a 2=b.b 2); 
commit; 
end; 

- 2. Large amount of data, use the merge into 
The create or replace procedure p_test 
Is 
The begin 
The merge into b using a on b.b=a.a 2 
The when matched then 
The set b.b 3=1 
; 
commit; 
end; 

CodePudding user response:

Given the amount of data is very small, you can use the update,

CodePudding user response:

It is good to update to update directly

CodePudding user response:

UPDATE the SET B B.B 3=1 WHERE B.B 2 IN (SELECT A.A 2 FROM A WHERE clause A.A 2=B2)