Updating a table from a join
Free teen chat rooms n ireland
I have known for a long time that you could update a SQL View in Microsoft SQL Server (back when I used to use Views), so it makes sense that you could update a JOIN, but it never occurred to me to try this...." not true: (to continue your last example): insert into s values('B', 3, 8); insert into s values('B', 4, 8); select * from s; F OLD_VALUE NEW_VALUE - ---------- ---------- A 1 5 A 2 6 A 3 7 B 3 8 B 4 8 UPDATE T SET VALUE = (SELECT NEW_VALUE FROM S WHERE VALUE = OLD_VALUE AND FLD = 'A') MERGE INTO T 2 USING (SELECT * FROM S WHERE FLD = 'A') S 3 ON (T. OLD_VALUE) 4 WHEN MATCHED THEN 5 UPDATE SET VALUE = S. There should not be any problem but it still not allowing me. So just curious to know what stops oracle for allowing it.(remember, merge IN GENERAL can insert, update and delete from the TGT)... do we update the TGT row twice - from 5 to 6, then 6 to 7 do we update the TGT row once and insert a duplicate ID? Attribute ID AND 6 CASE WHEN LOWER(s.value) = LOWER(p.value) THEN 1 ELSE 2 END = 2 7 ) 8 WHEN MATCHED THEN 9 UPDATE SET p. Value; CASE WHEN LOWER(s.value) = LOWER(p.value) THEN 1 ELSE 2 END = 2 * ERROR at line 6: ORA-38104: Columns referenced in the ON Clause cannot be updated: "P"."VALUE" SQL MERGE INTO p 2 USING s 3 ON ( 4 p. even for an instance if i want to update with a static value for the matching instance it dose not allows me. On 10g, Merge proves to be very fast than compared to update when the tables have significant size of data. 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.
I presumed therefore you really mean to join to T by some key and then update value.
Since I don't have any tables ready to play with, I have created three in-memory SQL tables: boy, girl, and relationship.