Oracle updating a view

6854933580_2c8b688306_z

There are a number of reason why we need views statement defining the view is executed, views can be created in your own schema (need CREATE VIEW privilege) or someone else's schema (need CREATE ANY VIEW privilege), you also need privileges on the underlining tables as well.

SQL ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 4999 | 185K| 30013 (34)| | | 1 | UPDATE | dest_tab | | | | | | 2 | NESTED LOOPS SEMI | | 4999 | 185K| 19 (6)| | | 3 | TABLE ACCESS FULL | dest_tab | 10000 | 332K| 18 (0)| | |* 4 | INDEX UNIQUE SCAN | source_tab_PK | 2500 | 10000 | 0 (0)| | | 5 | TABLE ACCESS BY INDEX ROWID| source_tab | 1 | 43 | 2 (0)| | |* 6 | INDEX UNIQUE SCAN | source_tab_PK | 1 | | 1 (0)| | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("ID"="TT"."ID") 6 - access("ST"."ID"=: B1) UPDATE (SELECT tt.id, tt.code, tt.description, AS st_code, st.description AS st_description FROM dest_tab tt, source_tab st WHERE = st.id) ilv SET = ilv.st_code, ilv.description = ilv.st_description; 5000 rows updated.

Does it mean delete all employees that are in department LCR?

Does it mean set to null the dept_code for employees in department LCR?

Using Views A view is a virtual table consisting of a stored query, it contains no data.

A view does not exist, basically its a definition defined within the data dictionary, lots of the DBA_ are views.

This restriction is, in some ways, obvious: if a single row from the child table could appear twice in the join then the two appearances could correspond to two different rows from the create table tab X (x1 number, x2 number); create table tab Y (y1 number, y2 number); insert into tabx values(1,1); insert into tabx values(1,99); insert into taby values(1,-1); insert into taby values(1,-99); commit; select y2, x2 from tab X, tab Y where tabx.x1 = taby.y1 ; Y2 X2 ---------- ---------- -1 99 -1 1 -99 99 -99 1 concept is not commonly known, but there are circumstances where it is the most efficient option for updating from one table to another. You’ll notice that I had included the name of the table owner in my original query – this is because I want to be able to run the update from another schema called This means I can update the relevant column from the child table, and see the columns from the parent and grandparent tables that I need to do the update.

You must have an account to comment. Please register or login here!