Hello, welcome to Appsloveworld Technologies ,In the session, we learn about updatable views before continuing with the session. I strongly recommend reading the below post first.
what are updatable views?
Let’s understand that with an example. I have a TblProfessor which has got Id, name, salary Gender, and University_Id columns.
Usually, salary is confidential information and we don’t want employees to be looking at other employee salary details.there is a requirement where a single user needs to know employee details.
In that case, if you grant the user access to the table, he will be able to see all rows and columns, including salary, and we don’t want that. So to avoid that, we can actually create a view that selects all the columns except the salary column.
Create View vWTblProfessor_withoutsalary_Detail as Select Id, Name, Gender from TblProfessor
And if you look at the select statement, it is selecting all the columns except the salary column. when you select data from that view, look at the result. We get all the columns except the salary column.
SELECT * from vWTblProfessor_withoutsalary_Detail
Now, if you look at the view itself, does it really store any data? No, “a view is only a store select statement. So it’s a virtual table. It’s not a real table.”
So when you execute the above query, the view actually gets its data from the TblProfessor table. So here for this view TblProfessor is the underlying base table.
Now when you issue select * from view name, that view is actually going to get its data from the underlying base table because the view itself doesn’t store any data.
So since you are able to issue a statement against a view which in turn will get its data from the underlying base table, can you update a view that will update the underlying base table? Absolutely. That’s definitely possible.
it’s possible to update a view, to insert data into your view, and to delete data from a view which in turn will actually do those respective operations on the underlying base table, in this case, TblProfessor. So when you update or when you insert into this view, it will actually update the underlying base table.
How to create updatable view in sql server?
Now let’s update the view. So I’m updating the view update view and we are saying the set name is equal to ‘Mark ‘where Id = 1.
Update vWTblProfessor_withoutsalary_Detail Set Name = 'Mark' Where Id = 1
Let’s execute the above query and then select data from the view
Now let’s select the data from The View. as you can in the above image ,name have been changed to ‘Mark‘.
Can we update real data in views?
Now let’s select data from the TblProfessor table and see if the underlying base table is updated. So if you look at the underlying base table TblProfessor table itself, the name is updated to ‘Mark‘.
So it’s possible to update the base table using views. In this case, we call The View as updatable view and in the SQL server, our views are updatable.
Delete and insert data from a view
Now we can also delete and insert data from a view which in turn will obviously delete and insert data from the underlying table. Let’s try to delete a record with id equal to 2 in this case Mackenzie record.
Delete from vWTblProfessor_withoutsalary_Detail where Id = 2
So let’s exclude and then select the data back from view and see if it’s really deleted.
As you can see in the above image ,Id=2 is deleted from the table. We can also insert data into the view.
SQL Updatable View with joined tables
Look at the view here, I’m creating. we have TblProfessor and TblUniversity table, on both of these tables. I’m creating a view which returns to me I’d, name, salary, gender, and University name.
So the first four Id, names, salary, and gender are coming from the TblProfessor table and the University name is coming from the TblUniversity table.
Create View vWTblProfessorDetail as Select Id, Name, Salary, Gender, UniversityName from TblProfessor join TblUniversity on TblProfessor.University_Id = TblUniversity.University_Id
we are creating a view which joins the TblProfessor table with the TblUniversity . if you have a view that is based on multiple tables and when you update that view, what happens? That’s what we will be looking at now.
Now if you look at Connor’s record with Id=4, Connor is currently within Arizona State University.
Let’s try to update his University to McGill University from Arizona State University ,but before we do that, note who else is within the Arizona State University along with Connor .
Chase is also within Arizona State University. So currently two Professor, Connor , and Chase , within Arizona State University.
when I issued the below update statement, we are actually updating Connor’s university to be McGill University from Arizona State University.
So only Connor’s university should be converted to McGill University. Chase should still remain Arizona State University.
Update vWTblProfessorDetail set UniversityName='McGill University' where Name = 'Connor'
So let’s update this and see what’s going to happen and then let’s select the data back from The View.
Connor’s university is set to it, which is good. But look at Chase’s university.we didn’t expect “McGill University” we expected this to be “Arizona State University“, but then our update statement didn’t update correctly. Something has gone wrong. So let’s analyze why.
when we issue, no matter whether your view is based on a single based table or multiple based tables, when you update the view, it’s going to update the underlying base tables.
Why? Because the view itself doesn’t store any data. By default, we can change that default behavior using materialist views. But by default, a view doesn’t really store any data.
So when you issue a select, insert, update or delete statement, these statements are executed against the underlying base tables.
so here, when you updated this view, it is actually behind the scenes updating the underlying base tables, TblProfessor and TblUniversity.
let’s select data from those tables and see what’s actually happening.
If you look at this, if you look at the TblUniversity’s data and look at this, Connor’s University_Id is 3, Chase’s University_Id is 3.
When you issued an update statement, when you say update, this view said University name is equal to “McGill University“, where the name is equal to Connor.
So for Connor, you are saying said University name is equal to “McGill University”. That means this University_Id for this Connor’s record should be changed from 3 to 7.
But in fact what has actually happened, when you updated the view, view incorrectly updated the underlying base table. it updates Arizona State University to McGill University in TblUniversity table.
So, if your view is based on multiple tables, and you want to update the view, then it may not update the underlying base tables incorrectly.
To correctly update a view based on multiple tables, we can use INSTEAD OF triggers are used.
Learn more about -INSTEAD OF triggers
Table Create Sql Script
/****** Object: Table [dbo].[TblProfessor] Script Date: 09/30/2020 5:35:11 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TblProfessor]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](max) NOT NULL, [Gender] [nvarchar](50) NULL, [Salary] [float] NULL, [University_Id] [int] NULL, CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[TblUniversity] Script Date: 09/30/2020 5:35:11 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TblUniversity]( [University_Id] [int] IDENTITY(1,1) NOT NULL, [UniversityName] [nvarchar](max) NULL, CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED ( [University_Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[TblProfessor] ON GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (1, 'Christopher', 'Male', 50000, 2) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (2, 'Mackenzie', 'Female', 60000, 2) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (3, 'Julia', 'Female', 40000, 4) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (4, 'Connor', 'Male', 30000, 3) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (5, 'Madeline', 'Female', 90000, 6) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (6, 'Easton', 'Male', 35000, 5) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (7, 'Gianna', 'Female', 40000, 7) GO INSERT [dbo].[TblProfessor] ([Id], [Name], [Gender], [Salary], [University_Id]) VALUES (8, 'Chase', 'Male', 32000, 3) GO SET IDENTITY_INSERT [dbo].[TblProfessor] OFF GO SET IDENTITY_INSERT [dbo].[TblUniversity] ON GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (1, 'Carnegie Mellon University') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (2, 'Michigan State University') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (3, 'Arizona State University') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (4, 'The Australian National University') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (5, 'The University of Arizona') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (6, 'The University of Melbourne') GO INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName]) VALUES (7, 'McGill University') GO SET IDENTITY_INSERT [dbo].[TblUniversity] OFF GO ALTER TABLE [dbo].[TblProfessor] WITH CHECK ADD CONSTRAINT [FK_TblStudent_University] FOREIGN KEY([University_Id]) REFERENCES [dbo].[TblUniversity] ([University_Id]) GO ALTER TABLE [dbo].[TblProfessor] CHECK CONSTRAINT [FK_TblStudent_University] GO
The post Can we write Insert,update and delete queries in views? appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- ASP .NET C# SQL return DBNULL on ExecuteScalar
- SQL Server Transaction inside stored procedure
- Return matching rows only SQL Server
- SQL deployment automation
- How to get list of all user's relation from hierarchy?
- Use of LENGTH in CTE
- SQL Server - Concatenate all child IDs into delimited string in parent record
- How to modify the following code to ignore rows with 0 and shows the output weekly
- 'Script Table as' in SSMS Not Preserving Collation
- Most popular pairs of shops for workers from each company
- Getting No of records inserted in Transaction
- 'Can not perform an aggregate function on an aggregate or a subquery' error on SQL Server
- How to use a CTE on multiple records
- Using SQL Profiler to create a trace file over several days
- Return value at max date for a particular id with null as maximum value in t-sql
- Query failed in (while using mssql_query() and php)
- Sql Calculating history from migrated data
- NHibernate Bag Mapping
- How to copy an ntext column's value to a new non-null column
- How to convert IF expression in sql to Derived column in SSIS
- Encrypting Windows Authentication connection strings
- Inserting Dynamically from one table into another
- When is SqlCommand.StatementCompleted supposed to fire?
- SQL SELECT and Stored Procedure integration
- How to reduce subqueries in a SQL View
- How do I use Char(39) in a sql statement where clause?
- From SQL Database to Excel with Python
- Entity Framework not returning related entity
- Use XML query as part of WHERE predicate
- Inserting unique value into database PGSQL/MSSQL
- Efficiency UPDATE WHERE ... vs CASE ... WHEN
- MS Access / SQL Server - VBA: Upload local file to filestream on remote SQL server
- I need to pull unique patients that meet certain criteria
- What is VARYING?
- How to have a good format of date?
- LinqDataSource set isolation level to READ UNCOMMITTED
- Is it possible to set the ROWLOCK table hint from a transaction isolation level in MSSQL?
- SQL Server 2012 geometry - Load schema using SqlDataAdapter
- 'insert into' from more source-tables
- SQL Server : charindex returning wrong index
- Linq to Entities with multiple related entities performance and UNIONs
- How to store timed data points in sql server
- query to return all records if parameter is null
- "SELECT * FROM..." VS "SELECT ID FROM..." Performance
- SQL Server - get all databases with MDF and LDF File Location
- SQL Server 2008 : group by , get max and sum up the values
- SELECT DISTINCT and ORDER BY number of same values
- SQL Server - Table Metadata
- How to get MS SQL Server to transparently use a CHECKSUM/hash index?
- Prevent circular reference in MS-SQL table
- How to use SqlDataReader with a parametrized query in c#?
- please help to revamp this simple but ugly query
- Inserting into multiple tables using SCOPE_IDENTITY
- How to store data when items passed are dynamic
- Assign value member from database to combobox
- How to Copy SSIS Package from MSDB Database to File System using ssms or ssis Package
- Execution plan not showing - CTRL + L shows only number of rows affected
- Sql Table data type for email address?
- How to get both the new pk and old pk when inserting a subset of a table into itself?
- Remove element from JSON array for whole table
- SQL Server Creating Problematic CSV files
- Stored Procedure Call from Lightswitch
- Trying to connect to Mssql server via knex
- How would someone create an ID for SQL?
- Get number of employees who worked in more than one department with SQL query
- Installing sql server express: VS2008 sp1 problem
- SQL Super Search
- Filling up a table in sql with data from another table only if it does not already exist
- Does sqlserver collation mean column names must be correct case? And how to deal with that
- XML File Translate with SQL query
- TSQL Check for Date difference except for Min Value
- Tracking Microsoft SQL Server Activity
- VBA strip string of double quotes when passed to stored procedure
- Retain primary keys in event rows needs to be restored
- How can I increment the value for each INSERT INTO iteration?
- Ignore first '0' on both sides of query
- Default column value based on the value of a identity column
- COALESCE function won't return CHAR(1)
- Can I return a column with it's value as two columns?
- SSIS: Flat File default length
- How to get a list of the unique keys for a table in SQL Server with PowerShell?
- Function sequence error, SQL state S1010 while executing a prepared Statement
- for loop execution in sql
- SQL: Combine Select count(*) from multiple tables
- How do I find the root of a hierarchy using LINQ and EF?
- Single Item Order - AdventureWorks
- SQL Server query select 1 from each sub-group
- How to add property to JSON object in SQL Server column
- Creating a lookup table from a column in table
- Why is "OR operation" or OR condition non-sargable?
- Loop/Iterate through date range while inserting
- SQL Server Change Tracking vs Replication vs Differential Backup
- PHP's SQLSRV driver would not complete a Stored Procedure query that works normally elsewhere
- SQL Server - Diff result from a simple stupid query?
- Cannot resolve column [name] in Java using Microsoft SQL Server and Javax.Persistence
- Why we need a primary key?
- NodeJS connect to SQL Server getaddrinfo ENOTFOUNT
- Calculating ad impression rank
- Select COUNT(*) of subquery without running it twice
- Get previous 12 months dates if given a datetime
- Building batch insert statement powershell to sql
- C# date formats for SQL Server
- How can I update a small field in a big SQL table if another field in the same row is changed by an external process?
- SQL SERVER - Finding Duplicates on XML Values
- Delete multiple records from different tables with SQL stored procedure
- The value is returned instead of NULL when using function with OUTER APPLY
- where clause on non-indexed column in sql
- How to use INSERT SELECT?
- While loop with if statements in SQL not working
- How to parse SOAP XML in SQL Server and show as table
- BULK INSERT across multiple related tables?
- Nested TransactionScope and/or nested connections causing MSDTC escalation
- Silent SQL Server 2014 installation fails with Access Denied on tempfile
- Why is this T-SQL query throwing an "aggregate function" error message?
- Microsoft SQL Server: How to export data from a database and import them to another database?
- How to use contains with single character in sql server?
- Custom SQL Server driver
- Two foreign keys on same table: how to implement on delete cascade?
- What's going wrong with this sql query?
- How can this query violate primary key?
- Limiting records in a one-to-many join
- How to filter a query on specific date format in mongodb?
- Symfony2 & MSSQL Server persisting dates
- SSIS Flat file could not deal with NUL (\x00) value?
- SQL Server - Split a field into 4 columns
- CPU usage goes up because of Entity Framework (connect to database)
- How can I move one of two perfectly identical rows into a new table through a query?
- Sum of minutes between multiple date ranges
- T-SQL select rows by oldest date and unique category
- Simple query that should be working is not
- Is there a way to specify a cascading delete, on a query by query basis?
- sql server error Login failded for user domainname\machinename$
- What does sp_CONFIGURE 'Database Mail XPs', 1 do
- Connecting to MS SQL Server on a remote desktop from Python
- How do i can show forecast data in years from row into column?
- Should I use a function or a parameter in my report?
- Identifying records with double space
- While more query executing, i got "Not allowed to change the 'ConnectionString' property."
- T-SQL Value from 1st select on each row in 2nd select of a union all
- Need date to show instead of being Null
- SSIS not using configuration when it is run on different server than where it was build
- How Can I save Data using Soap Jquery in SQL Server 2012?
- Lightswitch : can it create a real time SQLServer DB Monitoring application?
- Select table with name from column value
- How to move the database from one server to another server?
- Creating SQL table with column name "Return"
- SQL Server, nvarchar(MAX) or ntext, image or varbinary?
- How to get a string which contains a specific string between two full stop
- SQL Physical Connection is not usable
- Sql server: limit string_agg result