Pages

Wednesday, 4 April 2012

Updating key-preserved inline views

I learnt something today - there is a scenario where you need update privileges on a table you aren't updating.

Here is my example - I have a table TRAIN_APEX.RESOURCES that I would like to update based on TRAIN.RESOURCE_RATES. So the key factor here is my source table is in a different schema to my destination table - and I only have select privileges on resource_rates.

I've had a look through the documentation for views and update statements, and I can't see it as a pre-requisite.

Out of curiosity I defined the statement as an actual view to confirm the base table is key-preserved. I knew this already because I've successfully run this statement when the tables are in the same schema, or in scenarios where my account has UPDATE ANY TABLE.