Sql updating table from another table
I am think of the way without using cursor, script as below. I am getting current year data first into above table after I am trying to update previous year amount.
But the 2nd Where clause simply return the message of `more than one row is return', since the id is unpredictable and this create a `many to many' relationship in both tables. Many Thanks,(script)REM* the where-clause of the update cannot work UPDATE table b SET column_b1 = ( SELECT MAX(column_a1) FROM table_a a, table_b b WHERE a.id=GROUP BY a.id)WHERE table_IN (SELECT MIN(id)FROM table_a GROUP BY id); Your example is somewhat confusing -- you ask "update column a1 in table a where data in column b1 in table b" but your update shows you updating column b1 in table B with some data from table a. I this updation I want to find the equalent date/day in previous year corresponding to current year date and update the amount.
make sure to use CBO or you'll have to use HINTS as the RBO isn't smart enough to do the NOT IN really good. COM declare 2 l_icnt number := 0; 3 l_ucnt number := 0; 4 begin 5 for x in ( select * from t1 ) 6 loop 7 begin 8 insert into t2 ( object_id, object_name ) values ( x.object_id, x.object_name ); 9 l_icnt := l_icnt 1; 10 exception 11 when dup_val_on_index then 12 update t2 set object_name = x.object_name where object_id = x.object_id; 13 l_ucnt := l_ucnt 1; 14 end; 15 end loop; 16 dbms_output.put_line( 'Inserted '
Check out all our database development sessions at OOW19 This may seem like a simple question: Update Column a1 in Table A with all data in Column b1 in Table B. The Headoffice is merge the data into their system. For migration data first of all i create another temporary user named VISTEMP then cotinuing this kinds of codeinsert into VISTEMP. Its working very nicely.i will greateful to u forever.i was suffering by this problem for a long i am using the following /insert into VISTEMP. i could envision problems with only a second of granularity on a clock -- you could miss a record or two due to that.c) er? Here the source tables have data with leading spaces and the target data should be without spaces.If the user has update permission on table A, but only has select permission on table B, they cannot update via the first example. I tried the MERGE command and it is 10 fold fast, however i have few questions. I need to update 1 column (number) with a column from another table (around 350,000 records) based on a join value in the 2 tables. Can you think of a good way to do this in as little time as possible?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. In the first table (the 5,000,000 record table) all the records will be updated. (I'll be happy with anything under 20 minutes)Thanks in advance.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 thehistory table T2 ( 280 columns) that has 10 Million historical rows. May 10, 2004 - pm UTC nologging is not a hint.nologging will *never* be useful for an do realize your update sets a.mid_table_id to either: a) itself (you selected a.mid_table_id again) b) null if nothing exists in bprobably *not* what you meant?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 rows2.) If the row to load fails during Insert or update, can i trap that rows and put it into another table T3 .3.) How can i find out how many rows are inserted verser update.4.) Is Merge the best case in above scenario. I'll assume you *meant* b.mid_table_idas long as that column is not indexed, this won't take very long at all.update ( select a.mid_table_id a_mtid, b.mid_table_id b_mtid from big_table a, mid_table b where a.join_col = b.join_col ) set a_mtid = b_mtid;that assumes (naturally) that a primary/unique constraint exists on mid_table(join_col) -- if not, add one -- it *must be true* or the update would be ambigous (if many rows can exist in B, which one to use? Tom we have a table with the following structure :inv_id addr_1 addr_2 addr_3 addr_4 1 xxx null null yyy 2 null xxx null yyy 3 null null xxx yyy 4 null null null xxx 5 xxx null yyy null 6 xxx null yyy zzz output should be :inv_id addr_1 addr_2 addr_3 addr_4 1 xxx yyy null null 2 xxx yyy null null 3 xxx yyy null null 4 xxx null null null 5 xxx yyy null null 6 xxx yyy zzz null all Null values should move into one side, and all Not Null values should move into one side. Thanksdmv Also if your followup includes an example you want me to look at, I'll need it to have a create table, insert into statements and such that I can easily cut and paste into sqlplus myself (like I give you) in order to play with. update 2 ( 3 select id, olda, oldb, oldc, oldd, 4 a, 5 b, 6 decode(shift,0,c,d) c, 7 decode(shift,0,d,null) d 8 from ( 9 select t.*, decode(c,null,1,0) shift 10 from ( 11 select id, olda, oldb, oldc, oldd, 12 a, 13 decode(shift,0,b,1,c,2,d) b, 14 decode(shift,0,c,1,d) c, 15 decode(shift,0,d,null) d 16 from ( 17 select t.*, decode(b,null,decode(c,null,2,1),0) shift 18 from ( 19 select id, olda, oldb, oldc, oldd, 20 decode(shift,0,a,1,b,2,c,3,d) a, 21 decode(shift,0,b,1,c,2,d) b, 22 decode(shift,0,c,1,d) c, 23 decode(shift,0,d,1,null) d 24 from ( 25 select a olda, b oldb, c oldc, d oldd, t.*, decode(a,null,decode(b,null,decode(c,null,3,2),1),0) shift 26 from t 27 ) 28 ) t 29 ) 30 ) t 31 ) 32 ) 33 set olda = a, oldb = b, oldc = c, oldd = d 34 / 6 rows updated.