Proc sql updating multiple columns
you explicitly gate this circumstance in you filtering ON and WHERE clauses, which could be a performance problem (there will be a write, indexes will be updated, appropriate triggers will fire, ...) if not mitigated.
regards & thanks pjp June 29, 2005 - am UTC select as many old columns as you want and as many new columns as you want??? But when i did the same with the syntax given i got this error ora-01779 cannot modify a column which maps to a non key preserved value. I tried doing the same through this update ( select old.
WORKEXTENSION from TBL_VOTERS old, VOTERSWITHNUMBERS new where old.
00000 - "%s: invalid identifier"update ( select old.
WORKEXTENSION; But I was getting following error, by the Im on 10g R2 SQL Error: ORA-00904: "NEW"."WORKEXTENSION": invalid identifier 00904.
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!