Shikhar

• Dec 13th, 2006

 

You cannot update a view by writting a insert, update or delete query on the view. If you want to update some data in the view than you have to update the underlying tables.

Dev

• Dec 19th, 2006

 

Hi Shikar,

You are wrong. We can update a view by using insert,update,delete statements.

A view from a single table can be updated where as a view from multiple tables can not be updated.

Thanks

Dev.

ramesh t

• Dec 21st, 2006

 

the meaning of view is snapshot of the table .

 without disturbing the main table we done in view so we can perform the dml commnads in it.

 i think u understood

srikanth

• Dec 29th, 2006

 

view is collection of data but itis not stored in database.To simplfy the selected statements we can use view and itis snapshot for table

we can update view by using DML commands

rajesh

• Jan 3rd, 2007

 

If u have Created a View.. based on 2 Tables... u can use dml statements on that view.. but u can perform DML Operations Only on Key Preserved Table.

Shailendra Nigam

• Jan 4th, 2007

 

Hi

I am shailendra

its my op.

yes

you can update the view

suppose you can create the view

create view v1 as select ename,sal from emp

suppose you want to update the view according to salary

update v1 set ename='shailendra' where sal=800;

rahul Omanwar

• Jan 5th, 2007

 

Hello Shailendra,

 This is rahul.As per discussion on views. I want to share some thing. There are two types of views namely Simple and complex views.

U can perform DML operation on simple views which is based on single table and that view doesn't contain any single row function and any group by clause and it has to satiesfy integrity constraint also.

U can't perform DML operation on complex views becuase they are based on multiple table. U can achive this task by using Triggers(Instade of...).

If u have any suggestion on bellow discussion please send mail to rahul_omanwar@hotmail.com.

Thanks and regards,

Rahul Omanwar

mob no : 9423612912

srinivasaraok

• Feb 7th, 2007

 

Hi

Suppose you want update view means that time you need to use insted of trigger with that only possible to update view



Srinivas

Gman

• Feb 22nd, 2007

 

A materialized view can be updated.

sreenivas

• Mar 13th, 2007

 

Hai I am sreenivas kumar from Andhra Pradesh.
      Yes, it is possible to update the vies created on single table,if the vies are created on multiple tables then it is not possible to update ,if we want to update these type of vies then we have to create an instead of trigger ,then only we can update the views created on multiple tables.

ramyaselvi16

 Profile Answers by ramyaselvi16 Questions by ramyaselvi16

• Jun 24th, 2008

 

possible to update a view if it was created from one base table. otherwis  not possible to update a view.

pairohit123

 Profile Answers by pairohit123 

• Apr 22nd, 2009

 

A join view is defined as a view that has more than one table or view in its FROM clause (a join) and that does not use any of these clauses: DISTINCT,
AGGREGATION, GROUP BY, START WITH, CONNECT BY, ROWNUM, and set operations (UNION ALL, INTERSECT, and so on).

An updatable join view is a join view that involves two or more base tables or views, where UPDATE, INSERT, and DELETE operations are permitted. In order to be inherently updatable, a view cannot contain any of the following constructs:

1 A set operator
2 A DISTINCT operator
3 An aggregate or analytic function
4 A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause
5 A collection expression in a SELECT list
6 A subquery in a SELECT list
7 Joins (with some exceptions).

The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS contain information that indicates which of the view columns are updatable.   Views that are not updatable can be modified using INSTEAD OF triggers.

gourav

• Aug 19th, 2011

 

a view from more than 1 table can also b updated if it has primary key...

sohani

• Aug 20th, 2011

 

Http://psoug.org/reference/instead_of_trigger.html
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_8004.htm

example :

Code
CREATE TABLE family_details


( NAME VARCHAR(25),


AGE NUMBER, 


SEX CHAR,


STATUS VARCHAR2(8),


PRIMARY KEY (REL_ID));  


 


CREATE TABLE relation_type 


( rel_id NUMBER NOT NULL,


relation_type VARCHAR2(30),


 foreign KEY (rel_id) references sohani(REL_ID)


);  


 


CREATE VIEW family_view


AS


SELECT s.rel_id prim_id ,name,sex,age,status,relation_type ,r.rel_id sec_id FROM family_details s, relation_type r


WHERE r.rel_id = s.REL_ID


 


 


WILL PASS :INSERT INTO family_view (prim_id,name ,sex, age,status) VALUES (7,'mmm','F',34,'married')


( make sure ALL NOT nullable columns are included )


WILL PASS : INSERT INTO family_view (sec_id,RELATION_TYPE) VALUES (7,'granny')


 


WILL FAIL : INSERT INTO family_view (prim_id,name ,sex, age,status,relation_type,sec_id) VALUES (6,'kishan','M',32,'single','brother',6);


 

same case with update

fire_starter

 Profile Answers by fire_starter 

• Oct 9th, 2011

 

Hello,
Yes, you can modify views based on a single table or on a join on many tables but...:

A DML statement on a join view can modify only one base table of the view (so even if the view is created upon a join of many tables, only one table, the key preserved table can be modified through the view).
To be modifiable, a join view must also preserve a key from at least one of its tables.

For example, we create a view based on a join on two tables employee and retailer:

desc employee:
emp_id numeber4)
name varchar2(50)
salary number(9)

desc retailer
rtlr_nbr number(5)
name varchar2(50)
address varchar2(50)
salesperson_id number(4)

our view looks like this:

CREATE OR REPLACE VIEW v_rtlr_emp AS
SELECT c.rtlr_nbr, c.name, e.emp_id,
c.salesperson_id, e.name sales_rep
FROM retailer c JOIN employee e
ON c.salesperson_id = e.emp_id;

And let's say that the result is:
RTLR_NBR / NAME / EMP_ID / SALES_REP

104 / EVERY MOUNTAIN / 7499 / ALLEN
107 / WOMENS SPORTS / 7499 / ALLEN
201 / STADIUM SPORTS / 8421 / MICHAELS
300/ REBOUND SPORTS / 9122/ JOHNATAH

The primary key of the retailer is rtlr_nbr, as well as the key for the result of our join.

So only the retailer table can be modified through the v_rtlr_emp view.

Have a good day

Satyendra Kumar

• Oct 11th, 2011

 

Hi Shekhar,

As per my understanding we can update or insert or delete data from view but it will reflect to the underlying table too.

Satyendra

SIVA KARTHIK

• Dec 11th, 2011

 

Code
SQL> CREATE VIEW VIEW1 AS SELECT ENAME,SAL FROM EMP;


 


VIEW created.


 


SQL> SELECT * FROM VIEW1;


 


ENAME             SAL


---------- ----------


SMITH             531


ALLEN           20644


KING             3317


JONES            1974


BLAKE           36775


CLARK            1625


TURNER          19353


ADAMS             730


JAMES           12259


MILLER            862


WARD            16128


 


ENAME             SAL


---------- ----------


FORD             1991


SCOTT            1991


MARTIN          16128


 


14 rows selected.


 


SQL> UPDATE VIEW1 SET ENAME=HHH WHERE ENAME=SMITH;


 


1 row updated.


 


SQL> SELECT * FROM VIEW1;


 


ENAME             SAL


---------- ----------


HHH               531


ALLEN           20644


KING             3317


JONES            1974


BLAKE           36775


CLARK            1625


TURNER          19353


ADAMS             730


JAMES           12259


MILLER            862


WARD            16128


 


ENAME             SAL


---------- ----------


FORD             1991


SCOTT            1991


MARTIN          16128


 


14 rows selected.

ajay goel

• Jun 23rd, 2014

 

We cant execute DML on complex views but can execute on simple views ..however exceptions are always there. also sometimes we cant even update simple views like if we are updating a row and constraint is that there should not be a null value on the corresponding table. then we will also not be able to update views also so it is uncertain and depends on the situation

gauss786

 Profile Answers by gauss786 

• Jul 11th, 2014

 

use trigger INSTEAD OF on UPDATE of the view

moshe

• Mar 11th, 2016

 

No we cant use update statements on view because it is just select statement.

sumanamara2016

 Profile Answers by sumanamara2016 

• Mar 18th, 2016

 

View is log table with is having structure of "SELECT STMT DATA"
So view divided into 2 type:
a) Simple
b) Complex

In Simple you can add one table with N number of column

In Complex you have multiple table with different combination of N number of Column

Only in Simple you can update the view in complex is not possible.

shafiq

• May 17th, 2016

 

You can update tables through their views by simple update statement.

Muraleedhar Reddy B

• May 21st, 2016

 

We can perform DML on the view when
1.View is built based on a single table
2.Primary key of the table should be availbale in select clause used for view
We can not perform DML on the view when
1. View is bulit on multiple tables
However we can use trigger for DML operations on multi table view

mayur

• Jun 12th, 2017

 

Can we insert the value ino table by view when there is order by clause use in the view

Vikash

• Jul 12th, 2017

 

Yes, By instead of update trigger we can update view

Sushil Tembhekar

• Dec 16th, 2017

 

In SQL it is impossible to update the View.
but, By using "Instead of Trigger" clause in PL/ SQL, it is possible to update the View.

Asish

• Jan 7th, 2018

 

Yes, if the view is derived from one base table. Here you can execute all the DML opperation.
No, if the view derived from multiple base table.