It will be vastly faster and more understandable.i have two tables names DETAILS and STATUS DETALIS contains eid,ename,sal and STATUS contains eid,sal,updationdate.
with dbms_sql, you'll have one procedure that can process all 200 tables, it can describe what the cursor returns, it can bind to what the cursor returns and it can fetch an arbitrary row with an unknown (at compile time) set of columns. then run said code Tony Fernandez, if you are merging data into another table, don't use a cursor at all - If at all possible, query user_tab_columns to build a native dynamic MERGE statement to do the data move. Only nc type amt2 column in table1 should be updated 2.
select col2 col3 as result from table1 inner join (select distinct col_x2 from table2 where col_y2 = 'super OK') temp on temp.col_x2 = col_x1 where col4 = 'OK' How can I use join in my update statement? you update a join when you want the data from T1 to overwrite what is in T2 and you join T2 to T1 by T1's primary key. MAX_RANGE_CNTRL where jobs.rate_type = 'A' and jobs. nc2 nc3 nc4 nc5 nc6 nc1 matches c1, nc2 matches c2, nc3 matches c3 What I need to do is update particular records from one of the tables, but only the common columns The conceptual SQL should be some in the likes of (this is not a query don't hit me ;) it's what is intended to be done): Update table_extended set table_extended.nc2 = table.c2, table_extended.nc3 = table.c3 where table_extended.nc1 = table.c1 = @parameter Is there a simple way of doing this update? Many thanks, May 29, 2008 - pm UTC and if the other bottlenecks exist for merge, they shall exist for create table as select - plus reindex - plus grant - plus constrain what are your waits for, got a tkprof with waits? Is there any other way i can use to speed up the update by any efficient means! And why do i get an 'invalid column name' error when i run this?
now if i want to perform update operation on DETAILS table i need get information on MESSAGE table.
ITEM_NO * ERROR at line 4: ORA-00904: "CLASS_TEST"."ITEM_NO": invalid identifier Any help will be appreciated. If i am updating & using non key preserved tables then exists seems to be quicker one! update account_allocation2 set (sup_hyg_acc_policy,sup_hyg_acc_add)= ( select sup_hyg_acc_policy,sup_hyg_acc_add from acc_hyg_driver where acc_hyg_driver.account_number = account_allocation2.account_number and exists ( select '1' from acc_hyg_driver where acc_hyg_driver.account_number = account_allocation2.account_number )) any ideas?? merge into account_allocation2 using acc_hyg_driver on (acc_hyg_driver.account_number = account_allocation2.account_number) when matched then update set sup_hyg_acc_policy = acc_hyg_driver.sup_hyg_acc_policy, sup_hyg_acc_add = acc_hyg_driver.sup_hyg_acc_addmerge into account_allocation2 using (select * from acc_hyg_driver where account_number in (select account_number from account_allocation2) acc_hyg_driver on (acc_hyg_driver.account_number = account_allocation2.account_number) when matched then update set sup_hyg_acc_policy = acc_hyg_driver.sup_hyg_acc_policy, sup_hyg_acc_add = acc_hyg_driver.sup_hyg_acc_add when not matched then insert (account_number) values ( null ) the "using" query only needs to use the IN if acc_hyg_driver might have account numbers that are NOT IN account_allocation2, if acc_hyp_driver only has account numbers in that set, you can just use that table. But the volumes dealt with are account_allocation has 16 million acc_hyg_driver has 6 million and the update does around 6 million records, The current merge takes more than 5 hours with other bottle necks ( includes CPU sharing) which cant be helped!
ID = @parameter Hope I succeded in making it clearer.
What if i need to update a table using data from an external table?
from TBL_VOTERS old, VOTERSWITHNUMBERS new where old. Dear Tom, You mentioned previously that we need to have the table we are updating from to have a unique or primary key constraint.