Creating an update rule for a view

Posted on 2004-09-02
Last Modified: 2012-05-05
I have a view that looks like this.... called bb_po_view

 bb_child_po_bb_child_po.trx_id as "Unique",
 substr(bb_child_po_bb_child_po.create_date,1,16) as "Received Date" ,
bb_child_po_bb_child_po.sender as "Sender Name" ,
bb_child_po_status_id_concept.status_name as "Status" ,
bb_master_po_bb_master_po.st_control_num as "Control Number" ,
bb_child_po_bb_child_po.po_num as "PO #" ,
bb_master_po_bb_master_po.source_file_type as "Source Recieved" ,
bb_master_po_bb_master_po.source_file_uri as "Source URI" ,
bb_child_po_bb_child_po.file_type as "Document Sent" ,
bb_child_po_bb_child_po.file_uri as "Doc Location" ,
bb_child_po_bb_child_po.ui_filter_num as "UI"
 FROM bb_child_po_bb_child_po, bb_child_po_trx_id_concept, bb_child_po_status_id_concept, bb_master_po_bb_master_po
 WHERE bb_child_po_bb_child_po.trx_id = bb_child_po_trx_id_concept.trx_id
 AND bb_child_po_trx_id_concept.status_id = bb_child_po_status_id_concept.status_id
 AND bb_child_po_bb_child_po.master_trx_id = bb_master_po_bb_master_po.trx_id
 bb_master_po_bb_master_po.trx_id as "Unique",
 substr(bb_master_po_bb_master_po.create_date,1,16) as "Received Date" ,
bb_master_po_bb_master_po.sender as "Sender Name" ,
'Custom' as "Status" ,
bb_master_po_bb_master_po.st_control_num as "Control Number" ,
bb_master_po_bb_master_po.po_num as "PO #" ,
bb_master_po_bb_master_po.source_file_type as "Source Recieved" ,
bb_master_po_bb_master_po.source_file_uri as "Source URI" ,
bb_master_po_bb_master_po.target_file_type as "Document Sent" ,
bb_master_po_bb_master_po.target_file_uri as "Doc Location" ,
bb_master_po_bb_master_po.ui_filter_num as "UI"
 FROM bb_master_po_bb_master_po
 WHERE bb_master_po_bb_master_po.sender = 'Test'

when I try to update that view with the statement below, I get the following error

UPDATE bb_po_view
SET ui_filter_num = 3
WHERE  "Unique" = '01791694-4555-41a4-8546-16abe39e16d4'

Sep  1 17:28:58 [26007]: [31034] ERROR:  Cannot update a view without an appropriate rule

I am having problems creating this rule... can anyone tell me what it should be?


Question by:georgia
  • 3
LVL 22

Expert Comment

by:earth man2
ID: 11964518
Why don't you update the underlying table instead ?

update bb_child_po_bb_child_po set ui_filter_num = 3 where trx_id = '01791694-4555-41a4-8546-16abe39e16d4';

also view bb_po_view has no column called ui_filter_num you have aliased it as "UI".

Author Comment

ID: 11964605
unfortunately that is not an option due to a constraint within the system I am working - has to be updated through the view :(
LVL 22

Expert Comment

by:earth man2
ID: 11966208
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

To ensure you have a database that supports VIEW UPDATE RULES try this for starters.

CREATE OR REPLACE RULE bb_po_view_update AS ON update TO bb_po_view DO INSTEAD NOTHING;

how can you diferentiate between updates to bb_master_po_bb_master_po and bb_child_po_bb_child_po ?

You need to put that condition in the where claws.

LVL 22

Accepted Solution

earth man2 earned 250 total points
ID: 11966466
You need the unconditional do instead nothing rule to make updates to the view valid, see documentation on CREATE RULE.

Two more rules should be defined each with the WHERE clause specified for updates to the two underlying tables.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
calculate change in each day in postgresql 3 339
Need help with a few queries in Access 16 314
Need help with query for Pervasive 2 116
Resize varchar column in Postgre 4 216
Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: ( This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question