Cursors pl sql updating
It will need the primary key constraint on B though, if you do not have one BUT is unique, you can:[email protected] I can think of another instance (at least with v8.1.5) where the second example (updating the table via a subquery) works better than the first example (updating the joined tables). My last PL/SQL code was just a test as i was interested more in the performance during update, so i missed the commit point. )" would return more than one row in general leading to an error).... I have a table with about 5,000,000 records, the table have about 70 columns. I tried all kinds of variations with loading the data into collections and using index-by-varchar tables with no suceess in improving preformance by much.
If the user has update permission on table A, but only has select permission on table B, they cannot update via the first example. I tried the MERGE command and it is 10 fold fast, however i have few questions. I need to update 1 column (number) with a column from another table (around 350,000 records) based on a join value in the 2 tables. Can you think of a good way to do this in as little time as possible?
All fields in the temporary tables are varchar2) The actual Temp as well as Target table has 300 plus columns.2.) All rows failed during INSERT/u Pdate log them to a Fault table3.) Find out number of rows inserted verses update. The primary key columns of a should be mandatorily present in b, to ensure that only one row is updated. vnum_actual Value = 1000; After the distribution of the value 1000, the updated table should look like this: SNO ITEMCODE VALUE APPLIEDVALUE-------- -------- ---------- ------------ 1 item1 200 200 2 item2 100 100 3 item3 300 300 4 item4 200 200 5 item5 50 50 6 item6 200 150 7 item7 400 0 ( TOTAL:1000 ) Now, I can very well do this with a cursor. select * from t; SNO ITEMCODE VALUE APPLIEDVALUE ---------- -------- ---------- ------------ 1 item1 200 2 item2 100 3 item3 300 4 item4 200 5 item5 50 6 item6 200 7 item7 400 7 rows selected.
I am using the PL/SQL to handle this, the Inserts are fine but the Updates are very slow. The test PL/SQL is as follows -declare todate date; current_mode char(10) :='load'; exec_code varchar2(200); exec_message varchar2(2000); this_total number :=0; this_succ number:=0; this_update number:=0; this_fail number:=0; this_insert number:=0; this_start char(10) ; this_end char(10); this_table_name varchar2(10) :='test'; cursor c is select * from test_temp ; commit_point number :=0; test_case number := null; fault_id varchar2(30);beginselect to_char(sysdate, 'hh24:mm:ss') into this_start from dual;delete from test_load_result where table_name =upper(trim(this_table_name));commit;for xc1 in c loopthis_total:=this_total 1; commit_point:=commit_point 1; if commit_point 10000 then commit; commit_point:=0; end if; declare begin test_case := xc1.test_jdate; insert /* append */ into test( test_id, test_code ,test_case ,test_month ,test_jdate , sequence_no , check_status ) values ( trim(xc1.test_id), trim(xc1.test_code), to_date( to_char(to_date('','mm-dd-yyyy') ,'j') abs(test_case),'j'), upper(to_char((to_date( to_char(to_date('','mm-dd-yyyy') ,'j') abs(test_case),'j')),'mon')), trim(xc1.test_jdate), trim(xc1.sequence_no), trim(xc1.check_status) ); this_succ:=this_succ 1; exception when others then if sqlcode = -1 then declare begin update test set check_status = trim(xc1.check_status) where test_id = trim(xc1.test_id) and test_code=trim(xc1.test_code) and test_jdate = trim(xc1.test_jdate) and sequence_no =trim(xc1.sequence_no) ; this_update := this_update 1; exception when others then exec_code := sqlcode; exec_message := sqlerrm; select sysdate into todate from dual; if (this_update 0) then this_update := this_update - 1; end if; this_fail:=this_fail 1; fault_id:= trim(xc1.rms_index); insert into test_fault select (select sysdate from dual), st.* from test_temp st where trim(rms_index)=trim(fault_id); insert into test_exception( exec_date, exec_mode, table_name,rms_index, exec_code, exec_message) values (todate,current_mode,this_table_name,fault_id,exec_code,exec_message); end; else exec_code := sqlcode; exec_message := sqlerrm; select sysdate into todate from dual; this_fail:=this_fail 1; fault_id:= trim(xc1.rms_index); insert into test_fault select (select sysdate from dual), st.* from test_temp st where trim(rms_index)=trim(fault_id); insert into test_exception( exec_date, exec_mode, table_name,rms_index, exec_code, exec_message) values (todate,current_mode,this_table_name,fault_id,exec_code,exec_message); end if;end;end loop; this_succ := this_succ this_update; select decode(sign(this_succ -(this_update this_fail)),1,this_succ -(this_update this_fail),0) into this_insert from dual; select to_char(sysdate, 'hh24:mi:ss') into this_end from dual; test_results(todate,this_start, this_end, this_table_name, this_total, this_succ,this_insert, this_update, this_fail);commit;end;/ November 08, 2002 - pm UTC you do realize that /* append */ hint is just a waste of keystrokes right? Is there any way to do this with a single update statement? [email protected] merge into t 2 using ( 3 select sno, itemcode, value, 4 greatest( least( value, :x -(sum(value) over (order by sno)-value)),0) new_appliedvalue 5 from t 6 ) X 7 on (= ) 8 when matched then update set appliedvalue = new_appliedvalue 9 when not matched then insert (sno) values (null) -- NEVER can happen 10 / 7 rows merged.
Even the idea create a temporary table holding only primary key and Column b, and then apply cursor to it is slow. "c)if i have a composite key then"---where a.key1=b.key1 and a.key2=b.key2 and----)am i right for both insert/update by given ur advice? Another table summary of orders tt1 which is having current year summation and respective previous year summation columns. Type ------------------------------- -------- ------------ ORDER_NUMBER NUMBER(10) ORDER_DATE DATE CY_ORD_AMT NUMBER PY_ORD_AMT NUMBER Order date is current year date.
I am think of the way without using cursor, script as below. I am getting current year data first into above table after I am trying to update previous year amount.
Given that, we can:[email protected] select * from a 2 / ID A1---------- ------------------------- 1 Hello 2 World The following updates are equivalent as well - they do the same thing differently. March 22, 2004 - am UTC hows about this -- you write out in english what each one does -- write the "specs" if you will that describe what each query does.
It is best to use the above update of a JOIN if possible (optimizer has best chance of getting a good plan). then, we'll all know.(but #2 looks "wrong" if a's primary key is deptno,dname -- that means a given deptno can return many dnames and a dname many deptnos and hence the "deptno = ( select .... JOIN_COL); Now, if I do a regular simple update (like above) it takes for ever (never actualy had a chance to compleat it).
If you are at the conference, drop into the Groundbreaker area and say Hello. Every monththe client office is to give data(NEW & EDITED) "BY DATE RANGWISE" to the headoffice in CD. Thank u very much for ur kind & very helpful reply. Now we can update the join: update ( select a.pop, from taba a, gtt b where = ) set pop = cnt/and thats it. Hi Tom, Im selecting approximately 1 million records from some tables and populating another set of tables.
I have another table B containg 10000 records of incremented and edited records of A table. I am using the following codes to append data from B to A.--For incremental/New data-----insert into Aselect * from B where column_name NOT IN(select column_name from B);--For Edited Data-------cursore C_ABselect * from Aminusselect * from AFor R in C_ABloop Update A set....where ..loop End; It's working but taking a huge time/sometimes hang the computer. to update 10,000 rows in a 100,000 row table should take seconds (it'll be a direct function of the number of indexes). The number of rows in both the tables is same after porting.
Would u please help me how can i faster my procedure. But there is a posibility of one row inserted twice and another row may not be inserted at all.
Check out all our database development sessions at OOW19 This may seem like a simple question: Update Column a1 in Table A with all data in Column b1 in Table B. The Headoffice is merge the data into their system. For migration data first of all i create another temporary user named VISTEMP then cotinuing this kinds of codeinsert into VISTEMP. Its working very nicely.i will greateful to u forever.i was suffering by this problem for a long i am using the following /insert into VISTEMP. i could envision problems with only a second of granularity on a clock -- you could miss a record or two due to that.c) er? Here the source tables have data with leading spaces and the target data should be without spaces.
But I am trapped by the method that without using cursor to achieve it. REGISTRATION(BIN, NAME, NAME_ALIAS, COR_GROUP, AUTHOBY, AUTHODATE, CG_NAME, ADD1, ADD2, ADD3, TEL1, FAX1, ADD5, ADD6, ADD7, TEL2, FAX2, ADD9, ADD10, ADD11, TEL3, FAX3, TP_TYPE, TRD_LINC, TRD_FY, TRD_AUTH, IMP_REG, EXP_REG, REG_TYPE, TIN, STATUS, PRE_BIN, DATREG, STAT_CHNG, ACT_CODE, ACT_MULT, ITEM_TYPE, OLD_ACT, APP_CAT, LCODE, ISSUE_DATE, VREG, M_POSI, MFUNC, SFUNC, LAST_USER, LAST_ACCS, TREG, PAY_FREQ, CREG, EREG, OREG, OP_BAL, OP_BAL_DT)select BIN, NAME----from VIS. REGISTRATION); RUN and Computer in BIG Busy mood(Hang). REGISTRATION(BIN, NAME, NAME_ALIAS, COR_GROUP, AUTHOBY, AUTHODATE, CG_NAME, ADD1, ADD2, ADD3, TEL1, FAX1, ADD5, ADD6, ADD7, TEL2, FAX2, ADD9, ADD10, ADD11, TEL3, FAX3, TP_TYPE, TRD_LINC, TRD_FY, TRD_AUTH, IMP_REG, EXP_REG, REG_TYPE, TIN, STATUS, PRE_BIN, DATREG, STAT_CHNG, ACT_CODE, ACT_MULT, ITEM_TYPE, OLD_ACT, APP_CAT, LCODE, ISSUE_DATE, VREG, M_POSI, MFUNC, SFUNC, LAST_USER, LAST_ACCS, TREG, PAY_FREQ, CREG, EREG, OREG, OP_BAL, OP_BAL_DT)select BIN, NAME, NAME_ALIAS,---- from VIS. d) I don't program forms -- you can try otn.- Thank u very much for ur nice reply. I plan to use RTRIM function in the WHERE clause to get specific set records and then after fetching, Ill insert with a RTRIM on each column.