Updating a table from another table in oracle

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.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.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.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.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.

l_ucnt ); 18 end; 19 / Inserted 0 Updated 29317 PL/SQL procedure successfully completed. suggestion: put the constraints on the staging table -- SAME constraints, find them there, weed them out there. dear sir , Acually my problem is huge that that simple , i just try to give u a sample of my problem. that is, and will remain, my answer unless you give me a real world scenario that would demand otherwise (the 20 year old flag should NOT be stored, period) Hi I have three tables, A, B and C A is parent of B and C, A has a composite PK deptno and dname I would like to know if following delete SQLs are equivalent 1.------------------------------------------ delete b where (deptno, dept_name) in (select a.deptno, dname from a, c where a.deptno = c.deptno and a.dname = c.dept_name); 2.----------------------------------------- delete b where deptno = (select a.deptno from a, c where a.deptno = c.deptno and dname = dpt_name) and dept_name = (select a.dname from a, c where a.deptno = c.deptno and a.dname = c.dpt_name); Are they :-?

Search for updating a table from another table in oracle:

updating a table from another table in oracle-62

Hi Tom, I have a huge table similar to the following: eno ename dno sal mgr --------------------------------- 101 A 1 100 102 B 1 200 103 C 1 300 104 D 2 100 105 E 2 200 --------------------------------- Here I want to update the 'mgr' column with 'eno' value having largest 'sal' for each dno. ELIGIBILITY_KEY FROM STG_F_ELIGIBILITY_TRY B, stg_claim_try a WHERE A. your update speed will necessarily be gated by the performance of those queries... ELIGIBILITY_KEY B_ELIG_KEY FROM STG_F_ELIGIBILITY_TRY B, TMP_STG_CLAIM_TRY a WHERE A. PRIMARY KEY ) on commit delete rows; once in your database, then to update: insert into that gtt the job of A and B as above (add client_id to the select list) and the update the join of the gtt to the A table. DATE_SERVICE FROM STG_CLAIM_TRY A, STG_F_ELIGIBILITY_TRY B WHERE A. updating millions of rows is a couple minute process if o column is not indexed o you are not contending for the data updating millions of rows is a couple (hour|day|week|month|year) process potentially otherwise.

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating a table from another table in oracle”