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.
DB2 & UDB email list listserv db2-l LazyDBA home page