Updating joined tables

Rated 3.88/5 based on 537 customer reviews

And again problem with JOIN UPDATE comes when i want to insert MERGE INTO T USING (SELECT * FROM S WHERE FLD = 'A') S ON (T. OLD_VALUE) WHEN MATCHED THEN UPDATE SET VALUE = OLD_VALUE; UPDATE allows me to join on VALUE but MERGE dont i get the error.

[[email protected]] MERGE INTO T 2 USING (SELECT * FROM S WHERE FLD = 'A') S 3 ON (T. Here is an example : create table ttt as select object_id, object_name from all_objects; select count(*) from ttt; ------- 36561 alter table ttt disable constraint sys_c0074332; update ttt t set t.object_name = null; commit; create table sss as select object_id, object_name from all_objects; No indexes on the tables : --------------------------- update ttt set ttt.object_name = (select sss.object_name from sss where sss.object_id = ttt.object_id); -- took 177.296 seconds rollback; merge into ttt t using (select * from sss) s on (t.object_id = s.object_id) when matched then update set t.object_name = s.object_name -- took 0.907 seconds rollback; Create indexes and analyze the tables : ---------------------------------------- create index ttt_idx on ttt(object_id); create index sss_idx on sss(object_id); exec dbms_stats.gather_table_stats(ownname = true); update ttt set ttt.object_name = (select sss.object_name from sss where sss.object_id = ttt.object_id); -- took 1.593 seconds merge into ttt t using (select * from sss) s on (t.object_id = s.object_id) when matched then update set t.object_name = s.object_name -- took 0.906 seconds rollback; So the conclusion is that even with/without indexes Merge performed better than an update. Regards, Ravi Vedala CREATE TABLE p ( 2 id INTEGER NOT NULL, 3 User ID INTEGER NOT NULL, 4 Attribute ID INTEGER NOT NULL, 5 Value VARCHAR2(20) NOT NULL, 6 PRIMARY KEY (id), 7 UNIQUE (User ID, Attribute ID) 8 ); Table created.

assigned Team ID,resource.resource First Name,resource.resource Last Name FROM Resource Skill Mapping INNER JOIN Skill ON Resource Skill Mapping.skill ID = Skill.skill ID INNER JOIN Resource ON Resource Skill Mapping.resource Skill Map ID = Resource.resource Skill Map ID WHERE (Resource.resource Login ID LIKE '%Agent_asp%') AND (Resource.active = 1) I have not found any information on how to do this.

I'm sure there must be a way, test books don't cover this and the internet is no help here.

so why oracle is not allowing me to do a join update here.

given this as the case what is the best way to perform this update update t set t.value = (select new_value from s where t.value = s.value and s.fld_name = 'A') where exists (select new_value from s where t.value = s.value and s.fld_name = 'A') but using exists is very expencive for me. I modified your example - you update value, but you joined on value, so the update would do nothing.

Leave a Reply