Needs to be updated according to a userid excel in two columns, data lines, about 30 w about structure is as follows:
USERID COL1 COL2
111 AA AAA
222 BB BBB
.
Oracle database has a similar table t, but needs to be updated according to excel USERID COL1, COL2.
I update statement is generated by splicing formula in the excel, as follows:
='AA' update t set COL1, COL2='AAA' WHERE USERID='111';
='BB' update t set COL1, COL2='BBB' WHERE USERID='222';
.

Such copy to PLSQL run update statement, but the status bar will has always been the initializing state, such as card are there for a long time, finally can only be forced to exit, I tried to run more than 100 words is no problem, the statement is ok, bulk can perform much more,

Daniel, please advise what good method can batch update,

CodePudding user response:

Lock table,,,,,,,,,

CodePudding user response:

Update userid there is repeated, I have gone to heavy data, I also tried to update the commit after every sentence, also still won't do,,, stuck state

CodePudding user response:

Added pictures, stuck interface

CodePudding user response:

Well, I put the update statement in the BEGIN, END inside the code block, interface at least won't stuck now, has been performed, ten thousand rows of data in the 113 s, can also accept,
The Begin
The update... ;
commit;
The update... ;
commit;
The End;

CodePudding user response:

After each update statement with a commit, so that can release resources