you can also use an 'instead-of' trigger - so that any inserts/updates
to a view trigger a stored procedure to perform the action. This allows
the table to stay completely encapsulated from the application.


Ken

hsteiner wrote:
> No, a view of a JOIN is nonupdateable; however, you can still SELECT on that JOINVIEW and issue the UPDATE statement directly to the Table(s) involved. In your example, all you need do with the UPDATE statement is change the tablename to employee instead of EMP_DEPT.
>
> Best, Hal Steiner
>
> -----Original Message-----
> From: db2udbdba-ezmlmshield-x57183365.[Email address protected]
> To: [Email address protected]
> Sent: Tue, 29 Aug 2006 8:06 AM
> Subject: Updateable View
>
>
>
> Hi All,
>
>
>
> Can we perform an update / insert operation a view (which is made up of a
> join between 2 tables).
>
>
>
>
>
> For Eg:
>
>
>
> create table employee (empno int not null primary key, ename varchar(32),
> deptno int not null);
>
> create table department (deptno int not null primary key , dept_name
> varchar(32));
>
> alter table employee add foreign key(deptno) references department(deptno);
>
>
>
> create view emp_dept as select e.empno, e.ename, d.deptno from employee e,
> department D where e.deptno=d.deptno;
>
>
>
>
>
> Now if I write,
>
> Update emp_dept set ename='JIT' where empno=1;
>
>
>
> I get an error:
>
>
>
> DB21034E The command was processed as an SQL statement because it was not a
>
> valid Command Line Processor command. During SQL processing it returned:
>
> SQL0150N The target fullselect, view, typed table, materialized query
> table,
>
> or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a
> target
>
> for which the requested operation is not permitted. SQLSTATE=42807
>
>
>
>
>
> ============================================================================================================================
>
> Tech Mahindra, formerly Mahindra-British Telecom.
>
> Disclaimer:
>
> This message and the information contained herein is proprietary and
> confidential and subject to the Tech Mahindra policy statement, you may review
> at <a href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahindra.com/Disclaimer.html</a>
> externally and <a href="http://tim.techmahindra.com/Disclaimer.html">http://tim.techmahindra.com/Disclaimer.html</a>
> internally within Tech Mahindra.
>
> ============================================================================================================================
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
> ________________________________________________________________________
> Check out AOL.com today. Breaking news, video search, pictures, email and IM. All on demand. Always Free.
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> To post a dba job: http://jobs.lazydba.com
> To Subscribe : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>



DB2 & UDB email list listserv db2-l LazyDBA home page