Do Changes Made on Table Created by Joining a View and an Another Table Affect Original Table View Based On?

Suppose I have a view created on a table. If I change some record on my view, the original table is also changed too. What if I join this view with an another table and change some records on this result table I got by joining, would original table on which view based also be changed?

Answer

In principle, yes, if the views are constructed in such a way that the underlying tables are updateable through the view. For example, let’s say I have a table named TABLE_1 and create a view VIEW_1 as follows:

CREATE OR REPLACE VIEW VIEW_1 AS
  SELECT *
    FROM TABLE_1

If I issue the update statement

UPDATE VIEW_1
  SET FIELD_N = 'XYZ'
  WHERE KEY_1 = 123

Oracle is bright enough to pass the UPDATE through to the underlying table, and TABLE_1 will be updated.

A view of any complexity, however, will most likely contain operations that make the view non-updateable. So let’s say I have the following VIEW_2:

CREATE OR REPLACE VIEW VIEW_2 AS
  SELECT KEY_1,
         FIELD_N,
         SUM(SOME_OTHER_FIELD) AS OTHER_SUM,
         MIN(YADDA_YADDA) AS MIN_YADDA
    FROM TABLE_1
    GROUP BY KEY_1,
             FIELD_N

an UPDATE of this view will fail with an ORA-01732: data manipulation operation not legal on this view error. So whether you can update through a view or not very much depends on what operations the view is performing.

db<>fiddle here