Why can’t we update a view which has DISTINCT Clause in its definition? [closed]

I am not getting the logic behind the fact that a view can’t be updated if it has DISTINCT Clause in its definition . Please guide with an example .

Answer

If you have the table:

CREATE TABLE table_name ( value1, value2 ) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 1, 2 FROM DUAL UNION ALL
SELECT 2, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL;

and the view:

CREATE VIEW view_name ( value1, value2 ) AS
SELECT DISTINCT value1, value2 FROM table_name;

and you try to do:

UPDATE view_name
SET value2 = 2
WHERE ( value1, value2 ) IN ( (1, 1) );

Which row should be updated?

• Should it be the first row? or the second row? Either could be used as the DISTINCT value. In which case there would still be a DISTINCT row that has the 1, 1 pairing and it would appear that the update had done nothing.
• Both the first and second row? So that afterwards there would be no 1, 1 pairing?

The expected behaviour is ambiguous as either result could be a valid solution.

Oracle removes the potential ambiguity by disallowing updates on aggregated/distinct views.

From the Oracle UPDATE documentation:

• If you specify view, then the database updates the base table of the view. You cannot update a view except with INSTEAD OF triggers if the defining query of the view contains one of the following constructs:

  • A set operator
  • A DISTINCT operator
  • An aggregate or analytic function
  • A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
  • …