Updating a view in oracle

Rated 4.88/5 based on 656 customer reviews

You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view.i tried but it shows "missing right parenthesis"please send a reply to my id...thankx.... Hello, when I try this, it gives me the following error: SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table01779.00000 - "cannot modify a column which maps to a non key-preserved table"*Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table.*Action: Modify the underlying base tables directly. Hi Felix, Without seeing the actual query, it's near impossible to troubleshoot. First, verify that the table you are updating has a PK.

From the Oracle 10g SQL Reference: Notes on Updatable Views An updatable view is one you can use to insert, update, or delete base table rows.

I allready added a PK to the updated table, and still no luck :((my toad has just frozen trying to do something, so I can't give you the code, sorry)Hi, I've got nearly the same problem... (I work on Oracle 10g)Thx Hi, I've got nearly the same problem... (I work on Oracle 10g)Thx Doesn't work for me :update (select srhqt00.* from srhqt00 inner join (select trunc(sysdate)-1 dte from srhqt00) b on trunc(date_creation)= b.dte)cset c.statut = 'A';01732. Name Error at Command Line:16 Column:4Error report: SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table01779.

I'd like to "translate" this MS-SQL statement : 1: update employee_bonus 2: set bonus = e.bonus_value 3: from employee_bonus b 4: inner join employees e on b.employee_id = e.employee_id 5: where e.bonus_eligible = 'N'in "oracle" language... I'd like to "translate" this MS-SQL statement : 1: update employee_bonus 2: set bonus = b.bonus_value 3: from employee_bonus b 4: inner join employees e on b.employee_id = e.employee_id 5: where e.bonus_eligible = 'N'in "oracle" language... 00000 - "data manipulation operation not legal on this view"Oracle 10g R2I get the following error on Oracle 11g R1. Party p2 ON p1.objectid = p2.objectid WHERE p1.phone2 IS NOT NULL AND p2. 00000 - "cannot modify a column which maps to a non key-preserved table"*Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table.*Action: Modify the underlying base tables [email protected] The problem is that Oracle cannot guarantee that a single value will be found to set the Extended Attrs.

"updating primary key related to other foreign key.. Would you like to answer one of these unanswered questions instead?

it solved my problem to some i want to know can we update multiple values at the same time using a single querry. Take a look at this example:update ( select profit_bonus, merit_bonus from employee_bonus b inner join employees e on b.employee_id = e.employee_id where e.bonus_eligible = 'N' ) t set t.profit_bonus = 0, t.merit_bonus = 0; Hi David, This time where the employee is eligible for a bonus :-)update ( select b.bonus, e.salary from employee_bonus b inner join employees e on b.employee_id = e.employee_id where e.bonus_eligible = 'Y' ) t set t.bonus = 0.1 * t.salary Hope this helps!

Leave a Reply