Pl sql updating tables from another table

WORKEXTENSION; But I was getting following error, by the Im on 10g R2 SQL Error: ORA-00904: "NEW"."WORKEXTENSION": invalid identifier 00904. I don't think i can use the merge statement here since there are more than 1 condition that has to be met for the target table to be updated. now if i want to perform update operation on DETAILS table i need get information on MESSAGE table. 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. qty I want to update ORDERED_ITEMS table for a few records but exclude any book where BOOKS.bk_flag='X' Would you do this UPDATE ordered_item SET qty = new_qty WHERE order_date = xxxx and cust_id = xxxx and bkno not in (select bkno from books where bk_flag May 06, 2008 - am UTC tell me how you would do an update on a join here? D May 06, 2008 - am UTC and the types table plays into this HOW? You want to join basically three tables together, I can envision that jobs.item_no = class_test.item_no, but see NO WAY to add in TYPES to that join this question begs more questions than answers itself. Is it better to go for Create Table as Select (update info) ???? ) But i cant use the create for limitations on downtime of the table. Unfortunately i cant get the tkprof as i dont have access to.

COMP_BLOOMBERG_CODE = xls01.col1), COMP_AMS_DIVIDEND_FREQ=( SELECT col17 FROM xls01 WHERE INVST_COMPANY. Is there a way to do this or perhaps a better approach? June 05, 2013 - pm UTC write one bit of code to generate the other bits of code. write one procedure that will write 200 procedures.

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.

I use this statement: update table1 set col1 = col2 col3 where col4 = 'OK' and exists (select distinct 1 from table2 where table1.col_x = table2.col_x2 and table2.col_y2 = 'super OK') This update works, but is too slow... September 26, 2008 - pm UTC you can use merge merge into t1 using t2 on (t1.x = t2.a) when matched then update set y = t2.b; if there exist duplicated by t2.a (more than one row with t2.a having the same value), the merge will fail at runtime when it encounters you trying to modify Y in a non-deterministic fashion. Can we use the WHERE clause in the merge statement?

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.

COMP_BLOOMBERG_CODE = xls01.col1) WHERE EXISTS ( SELECT xls01. COMP_BLOOMBERG_CODE= xls01.col1); June 22, 2010 - am UTC not sure what your point is, but you have demonstrated perhaps the slowest way to achieve this update of a join. ref cursors have to be known at COMPILE time, you'd need the structure to be known when you COMPILE. loop over them in the dictionary, generate code for them. :) Hi Tom, There are 2 tables Table1 type cpty amt1 amt2 c a 10 0 c a 20 0 nc a 30 0 nc a 40 0 c b 50 0 c b 60 0 nc b 70 0 nc b 80 0 Table2 amt A cpty 100 a 115 a 120 a 125 b 130 b 140 b Trying to write an single update statement that will do the following 1.

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “pl sql updating tables from another table”