Insert, Update and Delete in a SQL View

Can you insert, update and delete in a View (SQL)? Yes, you can insert, update and delete a record in a view but there are some restrictions.

Use the following procedure to create a sample to understand how to perform such tasks.

Step 1: Create a schema of a table named "Employee" in your Database.

create table Employee
(
    ID int identity,
    Name varchar(20),
    Salary float,
    Department varchar(20)
)

Step 2: Insert some records into the table and then Table will look as from this command:

select * from Employee

Step 3: Create a view only for the selection of the data from the table by the following syntax:

Create view myView As select * from employee where salary < 30000

Step 4: Select the data from the view.

select * from myView

Question: How to insert, update and delete in a View (SQL) ?

Answer: Understand how to insert, update and delete in a View step-by-step.

1. Insetion intp a View

insert into myView (Name,Salary,Department)values('Narendra',5000,'Clerical')

After the insertion, select the view as well as table also as in the following:



2. Update in a View

Update myView set Salary=6000 where ID=11

 

After the update, select the view as well as table also as in the following:



3. Deletion in a View

Delete from myView  where ID=11

After the deletion, select the view as well as table also as in the following:

Note: You cannot insert, update and delete records in multiple tables when the view references more than one base table. You can only update columns that belong to a single base table.

For more info refer to my future article "Problem with views when you update the records".