How to Update Rows Using View in SQL

In this article I am going to explain how to update rows using view in SQL
  • 2819

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 update rows using view in SQL. To modify view we use ALTER VIEW statement. To delete view we use DROP clause. To create view we use CREATE VIEW statement with WHERE clause.

A Statement that create mcninvoices table

create table mcninvoices

(

invoiceid int not null identity primary key,

vendorid int not null,

invoiceno varchar(15),

invoicetotal money,

paymenttotal money,

creadittotal money

)

Statements that insert data in mcninvoices table

insert into mcninvoices values (20,'e001',100,100,0.00)

insert into mcninvoices values (21,'e002',200,200,0.00)

insert into mcninvoices values (22,'e003',500,0.00,100)

insert into mcninvoices values (23,'e004',1000,100,100)

insert into mcninvoices values (24,'e005',1200,200,500)

A Statement that is used to fetch data from mcninvoices table

inv111.jpg

A Statement that is used to create a view in SQL

create view updateview

as

select invoiceid,vendorid,invoicetotal,

invoicetotal-paymenttotal-creadittotal as baldue

from mcninvoices

where invoicetotal-paymenttotal-creadittotal>0

A Statement that show data in updateview

6.jpg

A Statement that is used to update a view in SQL

update updateview

set invoicetotal = invoicetotal + 100

where invoicetotal > 100

 
A Statement that show data in view after update

7.jpg


© 2020 DotNetHeaven. All rights reserved.