XXX Chats

cocky dating profile

Updating table in sql

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 rows 2.) If the row to load fails during Insert or update, can i trap that rows and put it into another table T3 . 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 either.The performance degrade when T1 has all rows for update. ") committing in for loop == terrible, horrible, really truly BAD idea. Anyway -- my suggestion -- lose the procedural code. COM begin 2 update ( select t1.object_name t1_oname, t2.object_name t2_oname 3 from t1, t2 4 where t1.object_id = t2.object_id ) 5 set t1_oname = t2_oname; 6 7 dbms_output.put_line( 'Updated '

If the table containing the changes can have MORE then one occurrence of the "primary key" of the other table -- no chance for a single statement. There are probably an infinite number of ways to do it, that was just the first that popped into mind.

If you tried: table t1( x int primary key, y int ); table t2( x int, y int ); insert into t1 values ( 1, 0 ); insert into t2 values ( 1, 100 ); insert into t2 values ( 1, 200 ); then update ( select t1.y t1_y, t2.y t2_y from t1, t2 where t1.x = t2.x ) set t1_y = t2_y would be "ambigous" -- no way we could know if y would end up with 100 or 200 -- hence we don't even permit it. Hi Tom, As you said in my case scenario I cannot use UPSERT(MERGE) and i have to write a pl/sql in case to achieve - 1.) Insert /Update from temporary table to actual table. make sure all 4 columns are the SAME TYPE of course.

Microsoft is conducting an online survey to understand your opinion of the Msdn Web site.

If you choose to participate, the online survey will be presented to you when you leave the Msdn Web site.

The staging instance tables looks exactly like the main tables other than a timestamp and an update_type column are added. 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? you 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 b probably *not* what you meant?

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.Consider (i'll be updating most all of the rows, well, all of them actually): [email protected] 3) you don't -- you could do the two step (update existing, insert new) 4) IMO -- if you have requirements that permit it -- yes. i just need to know the best way to update some column value that depends on other columns value in same table (or /and ) in other tables . March 22, 2004 - am UTC hows about this -- you write out in english what each one does -- write the "specs" if you will that describe what each query does. (but #2 looks "wrong" if a's primary key is deptno,dname -- that means a given deptno can return many dnames and a dname many deptnos and hence the "deptno = ( select .... JOIN_COL); Now, if I do a regular simple update (like above) it takes for ever (never actualy had a chance to compleat it).Updates/inserts/deletes come from another system into a staging instance. )" would return more than one row in general leading to an error).... I have a table with about 5,000,000 records, the table have about 70 columns. I tried all kinds of variations with loading the data into collections and using index-by-varchar tables with no suceess in improving preformance by much.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.Given that, we can: tky[email protected] select * from a 2 / ID A1 ---------- ------------------------- 1 Hello 2 World The following updates are equivalent as well - they do the same thing differently. REGISTRATION); RUN and Computer in BIG Busy mood(Hang). REGISTRATION(BIN, NAME, NAME_ALIAS, COR_GROUP, AUTHOBY, AUTHODATE, CG_NAME, ADD1, ADD2, ADD3, TEL1, FAX1, ADD5, ADD6, ADD7, TEL2, FAX2, ADD9, ADD10, ADD11, TEL3, FAX3, TP_TYPE, TRD_LINC, TRD_FY, TRD_AUTH, IMP_REG, EXP_REG, REG_TYPE, TIN, STATUS, PRE_BIN, DATREG, STAT_CHNG, ACT_CODE, ACT_MULT, ITEM_TYPE, OLD_ACT, APP_CAT, LCODE, ISSUE_DATE, VREG, M_POSI, MFUNC, SFUNC, LAST_USER, LAST_ACCS, TREG, PAY_FREQ, CREG, EREG, OREG, OP_BAL, OP_BAL_DT)select BIN, NAME, NAME_ALIAS,---- from VIS. In update portion i am facing another problem by ur recomended code. INSERT into abc values (RTRIM(col1), RTRIM(col2) .) There is another option where I can use SELECT RTRIM(col1), RTRIM(col2) from xxx where RTRIM(col1) = ‘yyy’ and then directly use insert into target table without RTRIM.It is best to use the above update of a JOIN if possible (optimizer has best chance of getting a good plan). REGISTRATION where not exists (select null from VIStemp. b)in privious question i told u that i am exporting incremental and edited data by using system date. In my B(big table record-79186) and in S(small table record-12871). Can you please tell me which option performs better if the number of records are in millions. I have a table t1 which is having all orders information.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. I this updation I want to find the equalent date/day in previous year corresponding to current year date and update the amount.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 the history table T2 ( 280 columns) that has 10 Million historical rows. For this I am using another table to find previous year dates for current year dates.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.

Comments Updating table in sql