COMP_BLOOMBERG_CODE = xls01.col1), COMP_AMS_P_BV_RATIO=( SELECT col15 FROM xls01 WHERE INVST_COMPANY. student_arrear regno papercode name m1 m2 m3 total result i need to update student_master from student_arrear those who have passed i have to match the regno and send sql query for this May 29, 2013 - pm UTC no create no inserts no look even more, no explanation of what "who have passed" - what defines "who have passed". The challenge is that when doing the update portion of merge, as in update table1_new set row = orig_row, orig_row needs to be defined as %rowtype of the table in the loop for 200 tables. COMP_BLOOMBERG_CODE = xls01.col1), COMP_AMS_LTM_P_E_RATIO=( SELECT col14 FROM xls01 WHERE INVST_COMPANY. ) merge into emp using (select emp.rowid rid, NVL2(test1.desc1,test1.desc1, SUBSTR(ENAME,1,5)) d1, NVL2(test1.desc1,test1.desc1, SUBSTR(job,1,5)) d2 from test1 ,emp where emp.deptno=test1.deptno( )) YOUR_QUERY on (emp.rowid = your_query.rid) when matched then update set dept_desc = d1, desc1 = d2; i am having 2 tables 1.student_master regno papercode name m1 m2 m3 total result 2. So table1 will be merged into table1_new, but would like to write generic procedure/package for all 200 at once and not 200 procedures. 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. 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.
COMP_BLOOMBERG_CODE = xls01.col1), COMP_AMS_52_WKS_L_PRIC=( SELECT COL7 FROM xls01 WHERE INVST_COMPANY. I think - based on your failed code snippet - that you wanted to update emp and set it equal to test1.desc1 if there was a matching record in test1, otherwise - default it to ename/job in the emp 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.
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), COMP_AMS_MARKET_CAP=( SELECT col11 FROM xls01 WHERE INVST_COMPANY. An outer join of A to (nothing) might return nothing.
COMP_BLOOMBERG_CODE = xls01.col1), COMP_AMS_6MTH_AVG_TRDNG_VOL=( SELECT col9 FROM xls01 WHERE INVST_COMPANY. from t where t.column = 5( ); you need to outer join to a "set" - an outer join of A to B returns every row in B at least once.