How to update, insert, delete the records from view

Photo by Luke Chesser on Unsplash
  • Views are logical tables which are based on one or more Tables, views, functions or synonyms.
  • View does not store the data itself.
  • The tables which are used in the view are called base table.
  • View retrieves the records at the time of execution and displays it in the result set.

Syntax:-

Example:-

Using DML Statements against the view :-

Yes, you heard it right. We can update/Insert/Delete the records of the table through view. Below are some restriction to it.

  • We can only update/Insert/Delete records of one table at a time if any type of join is used.
  • If we have used “with check options” then at the time of manipulating the data, it will check where clause of SQL statement mentioned in the view.
  • We cannot modify the data if group by or Aggregate functions are used in select statement of the view.
  • You can only manipulate columns which are given in the select statement of the view.

Examples :-

Without with check option:-

before update ;-

Update :-

After Update:-

As, It is visible that the record which is having Id value as 1 is updated with the email as Updated@gmail.com. The same logic applies to insert and delete as well.

With “with check option”:-

To demonstrate the With check option, we have changed the view as below.

Update:-

All the three records are updated now with userName as updateduserName.

After Update:-

Bhagavad Gita Verse:-

Ch.9V.22

love ❤ coding, solving some industry problems technologies: JavaScript, C#, Angular, PLSQL, Docker Want to learn: Python, Go language, AI, ML and Cloud