DB2 - View (Update/Insert/Delete)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

DB2 - View (Update/Insert/Delete)

Author

Topic

M2
Starting Member

22 Posts

Posted - 2003-12-18 : 21:24:09

I am beginner for DB2, I've created a View in DB2, however, I cant
perform operation such as update, insert, delete record from View.

CREATE TABLE AGENT1 ("ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 , INCREMENT BY 1 ) , AgentCode varchar(6),PersonID int)

CREATE TABLE Person1 ("ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 , INCREMENT BY 1 ) , Name varchar(50))

Insert into Agent1 (AgentCode,PersonID) values (‘e1111’,1)
Insert into Agent1 (AgentCode,PersonID) values (‘e2222’,2)
Insert into Agent1 (AgentCode,PersonID) values (‘e3333’,3)

Insert into Person1 (Name) values (‘1111’)
Insert into Person1 (Name) values (‘2222’)
Insert into Person1 (Name) values (‘3333’)

Create View AgentView (A_ID, AgentCode, PersonID, P_ID, Name) AS Select Agent1.ID, Agent1.AgentCode, Agent1.PersonID, Person1.ID, Person1.Name from Agent1 inner join Person1 on Agent1.PersonID = Person1.ID

Select * from AgentView

A_ID AGENTCODE PERSONID P_ID NAME
----------- --------- ----------- ----------- ----------------
1 e1111 1 1 1111
2 e2222 2 2 2222
3 e3333 3 3 3333

db2 => Update AgentView Set Name = 'qqq' Where A_ID = 2

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 view, typed table, materialized query table, or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a view, typed table, materialized query table, or staging table for which the requested operation is not permitted. SQLSTATE=42807

I have try to find some solution from internet (Article), but I fail. Some article said View is updatable, but some said not.

Anyone please guide me on this.

Thank you very much !! M2

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-12-19 : 07:22:01

You can't update THIS type of view....becuase it contains MORE than 1 table.

The database doens't know which table to update. I know the SQL statement only says to update the name...but in theory you could be looking to update the name from table1 and the address from table2 in the same query....and the Database manager isn't smart enough to spot that in this case only 1 table is being updated.

Try creating a view of table1 alone (no joins to other tables)....and issue an update statement on it....if that works...then the above is 100% accurate.

This is not a DB2 problem...it also affects SQL Server. This is why sometimes it says views are updatable and sometimes are not....it depends on the number of tables involved in the view.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources