Why derived table is updatable in Oracle?

Question

I have just found that derived table is updatable in oracle. When updating a derived table, the table it based is updated as a result.

Example UPDATE (SELECT * FROM T where T.col1='val1') as D SET D.col2 ='some_val'

After the example sql executed, table T would be updated.

I don't understand why Oracle support this feature.

As this question described, in mysql, derived table is not updatable.

Answer 1

It might be instructive to view Codd's Rules for relational databases.

Rule 7 is:

Rule 7: High-level insert, update, and delete:

The capability of handling a base relation or a derived relation as a single operand applies not only to the retrieval of data but also to the insertion, update and deletion of data.

My emphasis - and note that an important aspect that many people get wrong with relational databases is what the word "relation" or "relational" mean - relations are what are called tables in SQL. Many people assume that the "relational" in relational databases is the same sort of thing as a relationship in an Entity Relationship Model - and therefore leap to assuming that relational somehow relates to e.g. Foreign Keys.

Now, I'm not saying that SQL, either as embodied in the ANSI standard, or as embodied in any particular product is a complete implementation of Codd's Rules (indeed, Rule 6 is quite problematic), but there are at least attempts to implement them in some products.