Update statement using a join on a materialized view

From: Andy <andrewfaseuk_at_hotmail.com>
Date: 14 Mar 2006 03:14:41 -0800
Message-ID: <1142334881.825311.307370@u72g2000cwu.googlegroups.com>

Hi,

Hopefully someone can help me. I have 2 tables and 1 materialized view which is updated from a different database instance, the materialized view links the two other tables. So for example

Table_A

---

unique_key_A
table_A_parameter

Table_B

---

unique_key_B
table_B_parameter

Materialized_View_C

---

unique_key_C
unique_key_A
unique_key_B

other_stuff

I need to update records in Table A based on the value of table_B_parameter in Table B, the statement I would normally use for this would be:

update (
select table_A_parameter as param_to_update from Table_A, Table_B, Materialized_View_C where Table_A.unique_key_A = Materialized_View_C.unique_key_A and Materialized_View_C.unique_key_B = Table_B.unique_key_B and Table_B.table_B_parameter = 'X'
) set param_to_update = Y;

however whenever i do this I get a ORA-01031: insufficient privileges error I assume as oracle thinks i'm trying to update the materialized view, but i'm not ...

any ideas on how to do this kind of update ? can I grant update on the materialized view ?

any answers appreciated

Cheers

Andy Received on Tue Mar 14 2006 - 05:14:41 CST