How to Insert and Delete Rows in Base Table Using View in SQL
In this article I am going to explain how to insert and delete rows using view in sql.
Introduction
View in SQL is a virtual table and this table is created on the result set of SQL statement. View is similar to a normal table and also have rows and columns. View may be combination of some rows and columns of two or more than two tables. You can use SQL function and WHERE clause with View in SQL statement.
You can insert and delete rows into base table using view. To insert rows we use INSERT INTO statement. To delete row from base table using view we use DELETE clause. To create view we use CREATE VIEW statement with WHERE clause.
A Statement that create vendors table
create table vendors
(
vendorid int,
vendorname varchar(15),
vendorcity varchar(15),
vendorstate varchar(15)
)
|
Statements that insert data in vendors table
insert into vendors values (20,'vipendra','noida','up')
insert into vendors values (21,'deepak','lucknow','up')
insert into vendors values (22,'rahul','kanpur','up')
insert into vendors values (23,'malay','delhi','delhi')
insert into vendors values (24,'mayank','noida','up')
|
A Statement that is used to fetch data from vendors table
A Statement that is used to create a view in sql
create view vendorinfo
as
select vendorid,vendorname
from vendors
where vendorcity='noida'
|
A Statement that show data in view
A Statement that is used to insert row in base table using view
insert into vendorinfo values (40,'neha')
|
Statement that show data in vendor table after insertion of data
A Statement that is used to delete row from base table using view
delete from vendorinfo where vendorid=40
|
Statement that show data in vendor table after delete statement