How to update, insert, delete the records from view
- 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.