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:-

CREATE VIEW <View_Name>

AS

<SQL Query>

Example:-

CREATE VIEW v_users_without_critical_data

AS

SELECT s.email,s.Id,s.userName FROM Users s

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 :-

update v_users_without_critical_data

set email=’Updated@gmail.com’

where Id=’1';

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.

ALTER view [dbo].[v_users_without_critical_data] as

select Id,email,userName from Users

where Id in(1,2,3)

with check option

Update:-

update v_users_without_critical_data
set userName=’updateduserName’;

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

After Update:-

Bhagavad Gita Verse:-

Ch.9V.22

There are those who always think of Me and engage in exclusive devotion to Me. To them, whose minds are always absorbed in Me, I provide what they lack and preserve what they already possess.

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Journey to reverse engineer OpenShift operator

A “Kiss architecture”: Springboot + Angular

How the sales of Magic is affected by a new set release

A short (and simple) introduction to algorithm profiling

A compile-time test for mirrored Go structs

Testing your app’s data layer

HADOOP USING ANSIBLE

How to make a mobile application?

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Vishal Pathak

Vishal Pathak

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

More from Medium

Design Patterns

#01 Code Refactoring Shortcuts in IntelliJ

Epoch Time

GitHub— basic commands