Ms sql updating one table from another updating roster for madden 09
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?
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.
I have another table B containg 10000 records of incremented and edited records of A table. I am using the following codes to append data from B to A. consider: [email protected] merge into big_table B 2 using small_table S 3 on ( = ) 4 when matched then update set 5 b.owner = s.owner, b.object_name = s.object_name, 6 b.subobject_name = s.subobject_name, b.object_id = s.object_id, 7 b.data_object_id = s.data_object_id, b.object_type = s.object_type, 8 b.created = s.created, b.last_ddl_time = s.last_ddl_time, 9 b.timestamp = s.timestamp, b.status = s.status, b.temporary = s.temporary, 10 b.generated = s.generated, b.secondary = s.secondary 11 when not matched then insert 12 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, 13 OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, 14 GENERATED, SECONDARY ) 15 values 16 ( s. Surely next time, i will follow your instructions regarding create and insert statements, which helps you to answer quickly. Regards dmv Tom, I am using the cost based optimizer, I followed your suggestion and analyzed the 2 tables, this is what I get now. ELIGIBILITY_KEY FROM STG_F_ELIGIBILITY_TRY B, STG_CLAIM_TRY a WHERE A. The updating session is the only session in the db. create table testupdate (sno number(4), itemcode varchar2(8), value number(4), appliedvalue number(4)) insert into testupdate (sno,itemcode,value) values(1,'item1',200); insert into testupdate (sno,itemcode,value) values(2,'item2',100); insert into testupdate (sno,itemcode,value) values(3,'item3',300); insert into testupdate (sno,itemcode,value) values(4,'item4',200); insert into testupdate (sno,itemcode,value) values(5,'item5',50); insert into testupdate (sno,itemcode,value) values(6,'item6',200); insert into testupdate (sno,itemcode,value) values(7,'item7',400); SNO ITEMCODE VALUE APPLIEDVALUE -------- -------- ---------- ------------ 1 item1 200 2 item2 100 3 item3 300 4 item4 200 5 item5 50 6 item6 200 7 item7 400 Now: I'm writing a stored procedure in which I have to update the appliedvalue column of the above table.
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.
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.