Updating tables in sql from another table
l_icnt ); 17 dbms_output.put_line( 'Updated ' sql%rowcount ); 13 end; 14 / Updated 29317 Inserted 0 PL/SQL procedure successfully completed. 3.) How can i find out how many rows are inserted verser update. Thanks November 06, 2002 - pm UTC 1) you don't want to -- incrementation commits -- baaaaaddddd terrible practice. (answer = you cannot, the statement is transactional. one would need an example (complete, yet concise, with sample data and an explanation of how the data all fits together) hi tom , desc child_table ------------------- child_id number ; child_birth_date date; child_20_flag number(1) ; where the child_20_flag should indicate if the child reach 20 years old or not.
I would appreciate your help Thank declare exec_code varchar2(200); exec_message varchar2(2000); cursor cur is select * from t1; begin for x in cur loop declare begin Insert into T2(c1,c2,c3,c4,c5) values(x.c1, x.c2, x.c3 , x.c4, x.c5) ; exception when others then if sqlcode = -1 then declare begin update t2 set c3=x.c3, c4=x.c4 , c5=x.c5 where c1=x.c1 and c2=x.c2; exception when others then exec_code := sqlcode; exec_message := sqlerrm; insert into t3_exception( error_code, error_message) values (exec_code, exec_message); commit; end; else exec_code := sqlcode; exec_message := sqlerrm; insert into t3_exception( error_code, error_message) values (exec_code, exec_message); commit; end if; end; end loop; -- rollback; end; Danger Will Robinson! totally -- just update and insert and if you have 9i, just MERGE. I could develop procedural workarounds but we'd be right back at slow=true) 2) now you are back to procedural row by row processing. now , what is the best way to handle it automaticlly ? March 21, 2004 - pm UTC they are derived columns and should not be stored -- especially something like "20 years old flag" as that can change at any second.
why do you believe the update is slow -- what led you to that particular conclusion. ************************************************** This one take a fraction of a second to show first results SELECT B. DATE_SERVICE May 11, 2004 - pm UTC need ALL rows -- count(*) doesn't do it. DATE_SERVICE May 13, 2004 - pm UTC no, I mean: create global temporary table gtt ( b_elig_key ..., client_member_id ... DATE_SERVICE, 'YYYYMMDD')) May 15, 2004 - pm UTC how many rows to be updated is eligibility key indexed (are you mass updating an indexed key) is your update bumping into other row level updates.
Hi Tom, I have a huge table similar to the following: eno ename dno sal mgr --------------------------------- 101 A 1 100 102 B 1 200 103 C 1 300 104 D 2 100 105 E 2 200 --------------------------------- Here I want to update the 'mgr' column with 'eno' value having largest 'sal' for each dno. ELIGIBILITY_KEY FROM STG_F_ELIGIBILITY_TRY B, stg_claim_try a WHERE A. your update speed will necessarily be gated by the performance of those queries... ELIGIBILITY_KEY B_ELIG_KEY FROM STG_F_ELIGIBILITY_TRY B, TMP_STG_CLAIM_TRY a WHERE A. PRIMARY KEY ) on commit delete rows; once in your database, then to update: insert into that gtt the job of A and B as above (add client_id to the select list) and the update the join of the gtt to the A table. DATE_SERVICE FROM STG_CLAIM_TRY A, STG_F_ELIGIBILITY_TRY B WHERE A. updating millions of rows is a couple minute process if o column is not indexed o you are not contending for the data updating millions of rows is a couple (hour|day|week|month|year) process potentially otherwise.
I put a primary key on B(id) because if there is NO SUCH constraint -- the update is totally ambigous -- if there is more then 1 value of B1 in B for a given ID -- we cannot tell what row in B should be used to update the row in A. REGISTRATION where BIN NOT IN (select BIN from VIS. 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. 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.