Can we update a view in SQL

   Part - 18

Click here for you tube video answer:

--*************************************

-- Q18 -- Can we update a view in SQL?

--*************************************

Scenario 1: A plain view built on single base table

Yes, we can update a plain view build on top of a single base table    

DROP TABLE IF EXISTS tblEmployee 

create table tblEmployee (Empid INT, EMpname varchar(20), Deptid INT)

INSERT tblEmployee Values (1,'emp1',1),(2,'emp2',2), (3,'emp3',3)

select * from tblEmployee

Create view Employee_View AS

SELECT Empid,EMpname,Deptid FROM  tblEmployee

select * from tblEmployee

select * from Employee_View

select * from tblEmployee

Update Employee_View SET Deptid = 11 WHERE Empid = 1

select * from tblEmployee

Scenario 2: A view built on top of multiple base tables

           Yes, we can update a view built on top of multiple base tables if the Updating column set belongs to a single table 

DROP TABLE IF EXISTS tblEmployee 

create table tblEmployee (Empid INT, EMpname varchar(20), Deptid INT)

DROP TABLE IF EXISTS Dept 

Create table Dept (Deptid INT, DeptName Varchar(20))

INSERT tblEmployee Values (1,'emp1',1),(2,'emp2',2), (3,'emp3',1)

INSERT Dept Values (1,'HR'), (2,'IT')

-- Drop table Dept

SELECT * FROM tblEmployee

SELECT * FROM Dept

DROP VIEW IF EXISTS Employee_Dept;

Create view Employee_Dept AS

SELECT Empid,EMpname,DeptName FROM  tblEmployee E

JOIN Dept D ON E.Deptid = D.Deptid;

SELECT * FROM tblEmployee

SELECT * FROM Dept

SELECT * FROM Employee_Dept

           No, we can not update a view built on top of multiple base tables if the Updating column set belongs to multiple base tables  

UPDATE Employee_Dept SET EMpname = 'Emp11', DeptName = 'Human Resource'

WHERE Empid = 1

The above statement will throw below error:

SELECT * FROM tblEmployee 

UPDATE Employee_Dept SET EMpname = 'Emp11' WHERE Empid = 1

SELECT * FROM tblEmployee 

SELECT * FROM Employee_Dept -- view

SELECT * FROM tblEmployee -- base table

SELECT * FROM Dept

UPDATE Employee_Dept SET DeptName = 'Human Resource' WHERE Empid = 1

SELECT * FROM Dept

The above update statements updates the base tables as the update column list belongs to single base table