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.
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
Is sql procedural language?
why not null constraint showing its constraint type as 'c'
What are synonyms in sql?
How do I order columns in sql?
Can we group by two columns in sql?
how to get a list of indexes of an existing table? : Sql dba
What is sql exception?
Why do we need cursor in pl sql?
what are the authentication modes in sql server? : Sql dba
What is meant by temporal data?
What is record variable?
What is an escape character in sql?