Updating joined tables
Requesting a "estimated" plan does not involve executing the query, naturally.
If you get an error, either the view is not logically updatable, or the query processor can't tell that it is.
I've been working on understanding table joins better and last night I figured out one way I could insert a record into the joined table, but today I can't figure out how to update the joined table.
Does anyone know of any video's of Jeff's that would help me get a grasp on this?
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.
ERROR at line 3: ORA-38104: Columns referenced in the ON Clause cannot be updated: "T"."VALUE" Why is it so..
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. SQL July 16, 2012 - pm UTC the issue is the use of the p.value in the ON clause and in the UPDATE clause.
The plan shown will also show you how much of the view definition the query optimizer was able to remove (because it is redundant). FAX AS P_FAX FROM PERSON p INNER JOIN CONTACT c ON c. update (select t.value,s.new_value from t, s where t.value = s.value and s.fld_name = 'A') set t.value = s.new_value This update will fail if s.new_value is not unique. That is the subtlety behind the phrase, "..Database Engine must be able to..." The easiest way to be sure that a view is updatable is to request a pre-execution ("estimated") plan for the update query. id Data, -- PK of main table tab Data fi SL, fi Model, fi Claim Status -- FK to dim Claim Status FROM tab Data AS d INNER JOIN loc SL AS sl ON SL = SL INNER JOIN loc GSP AS gsp ON GSP = GSP INNER JOIN loc Country AS c ON Country = Country INNER JOIN loc Market Unit AS mu ON Market Unit = Market Unit INNER JOIN mod Model AS m ON Model = Model INNER JOIN dim Claim Status AS s ON Claim Status = Claim Status INNER JOIN tdef Product Type ON Product Type = tdef Product Product Type LEFT OUTER JOIN tdef Service Level ON d.fimax Service Level = tdef Service Service Level LEFT OUTER JOIN tdef Action Code AS ac ON d.fimax Action Code = Action Code UPDATE tab Data SET fi Claim Status = (SELECT id Claim Status FROM dim Claim Status WHERE Claim Status Name = 'Awaiting auth.') WHERE fi Claim Status=(SELECT id Claim Status FROM dim Claim Status WHERE Claim Status Name = 'Approved') updatable in practice, due to limitations of the query processor's reasoning.