Updating multiple tables in pl sql

09-Jun-2018 11:15

: OLD.c1); DBMS_OUTPUT. 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.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 statements running in triggers follow the same rules that standalone SQL statements do.You cannot use the update of a join in this case because the database currently wants you to JOIN to T on all of these columns - selecting where fld_name ='A' is insufficient.However, we can use merge to do this in bulk: update (select t.value,s.value new_value 2 from t, s 3 where = s.key_to_t 4 and s.fld_name = 'A') 5 set value = new_value 6 / set value = new_value * ERROR at line 5: ORA-01779: cannot modify a column which maps to a non key-preserved table ops$tkyte%ORA10GR2 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.Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it.While a trigger is , which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects. If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a )".

SQL statements running in triggers follow the same rules that standalone SQL statements do.You cannot use the update of a join in this case because the database currently wants you to JOIN to T on all of these columns - selecting where fld_name ='A' is insufficient.However, we can use merge to do this in bulk: update (select t.value,s.value new_value 2 from t, s 3 where = s.key_to_t 4 and s.fld_name = 'A') 5 set value = new_value 6 / set value = new_value * ERROR at line 5: ORA-01779: cannot modify a column which maps to a non key-preserved table ops$tkyte%ORA10GR2 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.Unlike a stored procedure, you can enable and disable a trigger, but you cannot explicitly invoke it.While a trigger is , which determines whether the trigger fires before or after the triggering statement runs and whether it fires for each row that the triggering statement affects. If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a )".When you are inserting records from another table, each value being inserted must be compatible with the type of field that will be receiving the data.