Updating data from one table to another in sql

Using cursor is ok, but it brings with a un-toleratable speed when operating on a large table. end loop End; It's working but taking a huge time/sometimes hang the computer. [email protected] update ( select upd.purchase_no u_pno, upd.amount u_a, 2 temp.purchase_no t_pno, temp.amount t_a 3 from upd, temp 4 where upd.urefitem = temp.urefitem ) 5 set u_pno = t_pno, u_a = t_a 6 / 635 rows updated. Is this time, 10 minutes for 12000 rows tables an acceptable (expected) time? Thanks Anandhi = b.cycle and b.site_id=44 and b.rel_cd in('code1','code2','code3') and b.groupid='123' 12,000 times?Even the idea create a temporary table holding only primary key and Column b, and then apply cursor to it is slow. Would u please help me how can i faster my procedure.u have given information 9i it same for oracle8i & dev6i.please help me Thank u very much for ur kind update information is really nice and working very insert----same prolem. That means -- just using math here -- that we have 600 seconds, 12,000 queries to run, 12000/60 = 20, so we are doing 20 per second -- or each query is taking 0.05 cpu seconds to run. do anything 12,000 times and you might have a problem tho! this might be one of the rare times that a temp table can be useful.In Microsoft Access 2007, I'm trying to update about 6000 records from one table with values from another table, but I'm having problems. When you have finished adding your tables, click on the Close button.Actually I am trying to update the Bot table MFG with the Big table MFG when Big. In this example, we've selected the Big and Bot tables.The records must be processed in order so that for instance, if a record is updated, deleted, inserted, then updated again (not likely, but it *could* happen) those operations should happen in the correct order. 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'll assume you *meant* b.mid_table_id as long as that column is not indexed, this won't take very long at all.Note - I didn't design this system, but I have to work with it. Elapsed: .22 that won't "hang the computer" for very long.. 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. Thanks dmv 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.Next, build the query like the one below: This query will update the MFG field in the Bot table with the value in the MFG field in the Big table when the PART values match.This may seem like a simple question: Update Column a1 in Table A with all data in Column b1 in Table B. I have a table named A containing say 100000 records. HSCODELIST 5 WHERE not exists 6 (SELECT NULL FROM VIStemp. Brao what I suggest then is not to do it in a single sql statement -- just proving that "there are exceptions to every rule". Type ----------------------------------------- -------- ---------------------------- BIN VARCHAR2(10) ACT_SL VARCHAR2(3) ACT_CODE VARCHAR2(11) ACT_VAL NUMBER(14,2) ENTRY_DATE DATE SQL DESC VIS. Type ----------------------------------------- -------- ---------------------------- BIN VARCHAR2(10) ACT_SL VARCHAR2(3) ACT_CODE VARCHAR2(11) ACT_VAL NUMBER(14,2) ENTRY_DATE DATE SQL UPDATE (SELECT DBHSCODELIST. the database needs to know that each row in dbhscodelist will map to AT MOST one row in hscodelist - this mandates a primary or unqiue key constraint on the join columns this is discussed in the original answer above.

sql%rowcount ); 13 end; 14 / Updated 29317 Inserted 0 PL/SQL procedure successfully completed. 3.) How can i find out how many rows are inserted verser update. Thanks November 06, 2002 - pm UTC 1) you don't want to -- incrementation commits -- baaaaaddddd terrible practice. (answer = you cannot, the statement is transactional. one 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 not.I would appreciate your help Thank declare exec_code varchar2(200); exec_message varchar2(2000); cursor cur is select * from t1; begin for x in cur loop declare begin Insert into T2(c1,c2,c3,c4,c5) values(x.c1, x.c2, x.c3 , x.c4, x.c5) ; exception when others then if sqlcode = -1 then declare begin update t2 set c3=x.c3, c4=x.c4 , c5=x.c5 where c1=x.c1 and c2=x.c2; exception when others then exec_code := sqlcode; exec_message := sqlerrm; insert into t3_exception( error_code, error_message) values (exec_code, exec_message); commit; end; else exec_code := sqlcode; exec_message := sqlerrm; insert into t3_exception( error_code, error_message) values (exec_code, exec_message); commit; end if; end; end loop; -- rollback; end; Danger Will Robinson! totally -- just update and insert and if you have 9i, just MERGE. I could develop procedural workarounds but we'd be right back at slow=true) 2) now you are back to procedural row by row processing. now , what is the best way to handle it automaticlly ? March 21, 2004 - pm UTC they are derived columns and should not be stored -- especially something like "20 years old flag" as that can change at any second.I am think of the way without using cursor, script as below. I don't understand what's the problem.i am going to give u full overview of my problem. The software is available in different portion of the country for data entry and report generation etc. What about: create global temporary table gtt ( id int primary key, cnt int ) on commit delete rows / you'll add that ONCE, it'll become part of your schema forever....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. Every month the client office is to give data(NEW & EDITED) "BY DATE RANGWISE" to the headoffice in CD. Now, you "two step" it: insert into gtt select b.id, count(*) cnt from tabb b, taba a where = and a.cycle = b.cycle and b.site_id = 44 and b.rel_cd in ( 'code1', 'code2', 'code3' ) and b.groupid = '123' and is null group by / that gets all of the id/cnts for only the rows of interest.

November 07, 2002 - pm UTC Oh, well -- then you cannot do it in a single update anyway -- as the table being updated would NOT be key preserved (and hence the result of the update would be very ambigous). I spent too many hours turning something like: I have a table like: [email protected] select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON I *need* to have I have a table: create table dept( deptno number(2) primary key, dname varchar2(14), loc varchar2(13) ); with this data: insert into dept values ( 10, 'accounting', 'new york' ); .... [email protected] select * from t; ID A B C D ---------- ---------- ---------- ---------- ---------- 1 1 2 2 1 2 3 1 2 4 2 5 1 2 6 1 2 3 6 rows selected.

Please or register to post comments
If spammers comment on your content, only you can see and manage such comments Delete all
How to perform update from two tables. -- SQL Server update from another table - sql server insert another. SQL update from one Table to another based on a ID. 
30-Oct-2018 18:07
Reply
SQL update from one Table to another based on a ID match. SQL Update column from one table to another. 1. Updating a table with data from another table. 0. 
30-Oct-2018 18:10
Reply
MidwayUSA is a privately held American retailer of various hunting and outdoor-related products. The company is headquartered in Columbia, Missouri, and sells in the continental United 
30-Oct-2018 18:15
Reply

Updating data from one table to another in sql introduction

Updating data from one table to another in sql