lifestyle dating radio Updating a table in oracle

There are methods via triggers, but I'm very very very much against that - not going to go there anymore.

create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL primary key, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.Here is a look back at those years, and here's to the next 40! 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. In 40 years, there's been some amazing functionality in the database. Here i am using oracle8i(8.1.6.0.0) and developer6i and windows2000 server. October 06, 2003 - am UTC obviously the two tables have DIFFERENT DATA???? REGISTRATION apparently has 12,871 records that match the S. It is apparent that when you join vis.registration to vistemp.registration -- 12,871 rows are "matched". there is nothing wrong here -- the computer is just doing exactly what you told it to do. HSCODELIST 2 (BIN, ACT_SL, ACT_CODE, ACT_VAL, ENTRY_DATE) 3 SELECT BIN, ACT_SL, ACT_CODE, ACT_VAL, ENTRY_DATE 4 FROM VIS. what could be the best approach to update this table. And moreover im using aggregate function like min(),max() group by p in each subquery like update upd set a = (select min(a) from a where upd.a=a.a group by a.p), b = (select (....) from b where upd.b=b.b), c = (select (........) from c where upd.c=c.c) Thanks for answering my question and we (our team) always admire your way of solving problems. October 08, 2003 - am UTC hscodelist is perhaps missing the mandatory primary/unique constraint on BIN?

For nearly half of that time, Ask Tom has been there. Elapsed: .82 [email protected] Thanks for kind reply. but when i using same user but different table its updating only the originally edited record and taking few this happening?? REGISTRATION, you said "that table has 79,186 records" -- only 12,871 (which happens to match the number of records in S) are updated. The differences in run time is easily explained -- it takes longer to update 6,000 times the data. we have tables upd ( which has to be updated) upd(p,a,b,c) Table a(p,a) Table b(p,b) Table c(p,c) i have an update like this update upd set a = (select (........) from a where upd.a=a.a), b = (select (........) from b where upd.b=b.b), c = (select (........) from c where upd.c=c.c) but the table a,b,c has around 3000000 records so the quesry takes loooong time to finish. Thanks brao October 06, 2003 - am UTC are there values of "p" in A that are not in B or C and vice versa -- eg -- if you join a to b to c without using full outer joins -- would you lose any rows? Yes there are some rows in the upd table which are not in a ,b or c. ENTRY_DATE S_ENTRY_DATE 3 FROM DBHSCODELIST, HSCODELIST 4 where DBHSCODELIST. BIN) 5 SET B_BIN=S_BIN, B_ENTRY_DATE =S_ENTRY_DATE; SET B_BIN=S_BIN, B_ENTRY_DATE =S_ENTRY_DATE * ERROR at line 5: ORA-01779: cannot modify a column which maps to a non key-preserved table what's wrong???i want to update employee code in parent table emp.i want all child table update automatically (including child of child also). table scripts create table EMP ( EMPNO NUMBER(4) not null, ENAME VARCHAR2(10) ); alter table EMP add constraint PK_EMP primary key (EMPNO); create table EMP_LOAN ( EMPNO NUMBER(4) not null, LOAN_NO NUMBER(2) not null ); alter table EMP_LOAN add constraint PK_EMPLOAN primary key (EMPNO, LOAN_NO) using index ; alter table EMP_LOAN add constraint EMP_LOAN_FK1 foreign key (EMPNO) references EMP (EMPNO); create table EMP_LOAN_TRA ( EMPNO NUMBER(4) not null, LOAN_NO NUMBER(2) not null, TRA_NO NUMBER(2) not null, AMOUNT NUMBER not null ); alter table EMP_LOAN_TRA add constraint PK_EMP_LOAN_TRA primary key (EMPNO, LOAN_NO, TRA_NO) using index ; alter table EMP_LOAN_TRA add constraint EMP_LOAN_TRA_FK1 foreign key (EMPNO, LOAN_NO) references EMP_LOAN (EMPNO, LOAN_NO); what is easy way to empno column ??? from E01 to E1001 From Chirag well, as we all know - primary keys are to be immutable - unchanging, constant.There are one column in each table, call id, to link them. --For incremental/New data----- insert into A select * from B where column_name NOT IN (select column_name from B); --For Edited Data------- cursore C_AB select * from B minus select * from A For R in C_AB loop Update A set....where ... this shows how I would approach getting the first two columns -- just add the other 2 and use merge to keep filling temp -- and then update the join: [email protected] -1 5 group by urefitem ) b 6 on (temp.urefitem = b.urefitem) 7 when matched then update set amount = b.sum_total 8 when not matched then insert (urefitem,amount) values ( b.urefitem, b.sum_total) 9 / 398 rows merged. using a cursor means you are back to "slow=very_true" you already WERE updating on a bulk basis??? But when I run the following query, it takes up 50% of CPU. tab A has these columns: id, cycle, pop tab B has these columns: id, cycle, site_id,rel_cd,groupid update tab A a set pop= (select count(*) from tab B b where b.id=and a.cycle = b.cycle and b.site_id=44 and b.rel_cd in('code1','code2','code3') and b.groupid='123') where pop is null and id in(select id from tab B); call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 2 496.35 499.54 7530955 9902630 76532 11444 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 3 496.35 499.54 7530955 9902630 76532 11444 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 305 Rows Row Source Operation ------- --------------------------------------------------- 1 UPDATE tab A 11445 MERGE JOIN 5942 VIEW VW_NSO_1 5942 SORT UNIQUE 31227 TABLE ACCESS FULL tab B 17385 SORT JOIN 12601 TABLE ACCESS FULL tab A Now my questions are: 1. We have several such updates that creates the same problems on the server from time to time and I would appreciate some guidance to resolve this.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?You will be given questions that you need to solve.