Updating data from one table to another
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.Continue Reading Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.
I have a SQL query where I am trying to update a column in a table (tbl A) from data in another table (tbl B).Visit Stack Exchange Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Sign up to join this community I have one table A has column (id, field_1,field_2), and another table B has column (id,field_2) Now I want to merge table B to A, that means i want to update field_2 in table A to value of table B. BTW I am using oracle Thanks Now, each time the above is executed, it will do it across all rows in the table.If this is something you need to do all the time, I would suggest something else, but for a one-off or very small tables it should be sufficient.Additionally -- given the way the where and set clauses are CODED in the above -- it would succeed. For this I am using another table to find previous year dates for current year dates.The query would in fact execute without any error messages since the correlated subquery in SET clause returns EXACTLY one row for each row in B and the where clause executes without error. Lets say you have a table A ( id int, a1 varchar2(25) ) and a table B ( id int PRIMARY KEY, b1 varchar2(25) ). 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 would need an example (complete, yet concise, with sample data and an explanation of how the data all fits together) hi tom , desc child_table-------------------child_id number ;child_birth_date date;child_20_flag number(1) ; where the child_20_flag should indicate if the child reach 20 years old or , what is the best way to handle it automaticlly ?