a. Can you delete data from a View. b. If Yes, can you delete it if there are

a. Can you delete data from a View.
b. If Yes, can you delete it if there are multiple tables
c. If No, can you delete if there is single source table
which is joining.

Question Posted / sql2000

4 Answers
10577 Views
CGI, IBM, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

a. Can you delete data from a View. b. If Yes, can you delete it if there are multiple tables c. ..

Answer / haarika valasa

IF view is created based on only one base table we can
perform all DML operations.

If view is created based on multiple table using joins and
goup by functions etc..than we cannot perform directly DML
operations.

Using Instead of Trigger we can perform DML operations on
complex view.

Is This Answer Correct ?

24 Yes

0 No

a. Can you delete data from a View. b. If Yes, can you delete it if there are multiple tables c. ..

Answer / sql2000

a. Yes
b. NO
c. Yes

Is This Answer Correct ?

6 Yes

0 No

a. Can you delete data from a View. b. If Yes, can you delete it if there are multiple tables c. ..

Answer / pankaj

you can remove the rows from the view if it does not containing any of the following.
1> group function
2> group by clause
3> distinct keyword
4> rownum keyword

Is This Answer Correct ?

1 Yes

0 No

a. Can you delete data from a View. b. If Yes, can you delete it if there are multiple tables c. ..

Answer / suman

Yes we can insert records if view having one to one table. but we cannot create insert if we have multiple table join query

create or replace view v_emp
as select * from emp;

insert into v_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
values(1111,'suman','CLERK',1111,SYSDATE,11,1,20);

create or replace view v_emp
as select empno,ENAME,job,deptno from emp;

insert into v_emp(empno,ename,job,deptno)
values(1112,'suman','CLERK',20);

SELECT * FROM V_EMP WHERE EMPNO=1111;
SELECT * FROM V_EMP WHERE EMPNO=1112;

create or replace view v_emp_dept
as select empno,e.deptno,dname from emp e, dept b where e.deptno=b.deptno;

insert into v_emp_dept(empno,deptno,dname)
values(1112,'suman','CLERK');

select * from v_emp_dept where empno=1111;
01776. 00000 - "cannot modify more than one base table through a join view"
*Cause: Columns belonging to more than one underlying table were either
inserted into or updated.
*Action: Phrase the statement as two or more separate statements.

Is This Answer Correct ?

0 Yes

0 No

Post New Answer