Updating a table from another table

Oracle will return ORA-01031 (insufficient privileges). Hi Tom, I have a related question for an UPDATE - that takes unexpected long time. You said to use Oracle9I Merge statement when inserting or updating the data from a stagging table to history table. I this updation I want to find the equalent date/day in previous year corresponding to current year date and update the amount.I am using Table T1 to insert or update the data inthe Table T2 (which has a unique key column -c1 c2) in oracle 8.1.7. COM merge into t1 2 using t2 3 on ( t2.object_id = t1.object_id ) 4 when matched then 5 update set t1.object_name = t2.object_name 6 when not matched then 7 insert (object_id, object_name) values( t2.object_id, t2.object_name); 29317 rows merged. In my case Table T1 (staging table with 300 columns) will have 200 thousand rows every day for insert or update to the history table T2 ( 280 columns) that has 10 Million historical rows. For this I am using another table to find previous year dates for current year dates.w=300" data-large-file="https://suvendugiri.files.wordpress.com/2012/01/001.png? w=443" class="size-full wp-image-106 aligncenter" title="Table2" src="https://suvendugiri.files.wordpress.com/2012/01/002.png? w=869" alt="Table2" / We need to update the values of column ‘T2Description’ of table T2 which currently have null values ,with the values from table T1(field- T1Description). w=545" class="alignleft size-full wp-image-110" title="Result" src="https://suvendugiri.files.wordpress.com/2012/01/003.png? w=869" alt="Result" srcset="https://suvendugiri.files.wordpress.com/2012/01/003545w, https://suvendugiri.files.wordpress.com/2012/01/003.png? w=150 150w, https://suvendugiri.files.wordpress.com/2012/01/003.png? Note that, here we have 2 common field types i.e, Category and Description. The update script will look like- Result : Result " data-medium-file="https://suvendugiri.files.wordpress.com/2012/01/003.png? w=300" data-large-file="https://suvendugiri.files.wordpress.com/2012/01/003.png? Since you can't list more than one table in the Oracle UPDATE statement, you can use the Oracle EXISTS clause. When you sign in to comment, IBM will provide your email, first name and last name to DISQUS.

But I am trapped by the method that without using cursor to achieve it. I have another table B containg 10,000 records of incremented and edited records of A table. I am using the following codes to append data from B to A. Normally, I would try to use a single sql statment -- here, due to the "data being spread all over the place", and being distributed and all. We have a 2 CPU machine where at normal times, the topmost entry in top command shows only .2 or .3 percentage of CPU use. This is on a test database where nothing else is going on concurrently.That information, along with your comments, will be governed by DISQUS’ privacy policy.By commenting, you are accepting the DISQUS terms of service.The pl/sql block first tries to insert if exception occur because of sqlcode = -1 then it knows it is for update. Either: o move the error log into an autonomous transaction o just LOSE the commit all together (without changing the outcome of your program at all!! o you hit an error on the update o you log it and commit o you subsequently (later) hit an ora-1555 on the SELECT and bump out of the loop whoops -- you processed 1/2 of the table. Since I am inserting a massive amount of data, using Merge - 1.) How can I commit at every 1000 rows 2.) If the row to load fails during Insert or update, can i trap that rows and put it into another table T3 . But I am not able to identify the correct record to update. Thanks January 02, 2004 - am UTC and i, not knowing how your data all fits together, cannot say either.The performance degrade when T1 has all rows for update. ") committing in for loop == terrible, horrible, really truly BAD idea. Anyway -- my suggestion -- lose the procedural code. COM begin 2 update ( select t1.object_name t1_oname, t2.object_name t2_oname 3 from t1, t2 4 where t1.object_id = t2.object_id ) 5 set t1_oname = t2_oname; 6 7 dbms_output.put_line( 'Updated '