Updating multiple fields in oracle


06-Mar-2018 02:06

l_cnt

I can not do a one hit update outside of the cursor loop.

The example I have seen show only one line command after FOR ALL so its good to know that FOR ALL can handle a multi line transaction with END LOOP. (calling some procedures / functions) inserting into another table end loop; It processes 6 lakhs records. DEPTNO,3); 40 END; 41 END LOOP; 42 END; 43 / PL/SQL procedure successfully completed. COUNT UPDATE emp SET sal=NULL WHERE emp_id=t_id(i); FORALL i IN 1 .. COUNT DELETE FROM dept WHERE emp_id=t_id(i); Regards, Ammu CREATE OR REPLACE procedure proc(p_trade_dt in number) is cursor cur_a is select val ,tradedate ,trade_dt_key ,(price1 price2) price ,((price1 price2)/2 mid_price ,decode( typ, 'B', QTY, 0 ) buy ,decode( typ, 'S', QTY, 0 ) sell ,(decode( type, 'B', QTY, 0 ) decode( typ, 'S', QTY, 0 )) buy_sell from price_data where trade_dt_key=p_trade_dt; cursor cur_b is select * from price_new where trade_dt_key = p_trade_dt; type price_type is table of cur_a%ROWTYPE; price_tab price_type; begin open cur_a; fetch cur_a bulk collect into price_tab; close cur_a; FORALL i IN 1 .. COUNT update price_new pn set price1 = -- sum(mid_prc) for the time period in --where clause where val = price_tab(i)and (pn.last_minus_5 January 24, 2009 - pm UTC I cannot evaluate code that doesn't make sense or compile. oh, and if you expect "sample code", I expect 'create table' Hi tom, Cant we use WHEN clause in FORALL during UPDATE also. l_fileid.count insert when (address_flag='Y') then into emp_addr( address, city, empno ) values ( address, city, s.currval ) select l_lname(i) lname, l_fname(i) fname, l_address(i) address, l_city(i) city, l_phone(i) phone1,l_address_flag(i) address_flag, l_phone_flag(i) phone_flag from dual; Thanks. I want to update based on when clause in a single table. It has nothing to do with performance, everything to do with "what is your transaction, your logical unit of work. I do not know what your data looks like, what your process is like, what your update involves, how your update works. I did a search for why use packages here on asktom and on the first page of hits was: Procedure vs Package Hello, could the use of rowids in this case be potentially dangerous? I was also thinking of maybe truncating the table and rather inserting the records.



Home SQL How to update Multiple Columns in Oracle. wanted to show you how to update multiple columns in a. put a parenthesis around the fields we are.… continue reading »


Read more

FORALL Update - Updating multiple columns; Breadcrumb. Thanks for your support - we'll keep working harder for you to be more successful with Oracle.… continue reading »


Read more

Oracle Certification, Database Administration, SQL, Application, Programming Reference Books Updating multiple fields with an embedded SELECT… continue reading »


Read more

Updating Multiple Columns from another table - Need Oracle format. Ask Question. up vote 8 down vote favorite. 2. Oracle update from multiple tables. 1.… continue reading »


Read more