Re: update record with two-column primary key

Please keep replies on the list, it's more likely to find an answer
with many eyes on the problem.

On Nov 12, 2007 4:13 PM, Charles Mortell <cmortell@apt-cafm.com> wrote:
> Thanks for your response, Scott.
> The primary keys of projectdata.business_list are item_id and business. They
> are from the two tables I am linking. Item_id is unchanged.

With this query:

> UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and
> business = 7;

And this View and rule:

> CREATE TABLE projectdata.data_business_list
> (
>   item_id int4 NOT NULL,
>   business int4 NOT NULL,
>   comments varchar(256),
>   CONSTRAINT data_business_list_pkey PRIMARY KEY (item_id, business)
> )
>
> CREATE OR REPLACE VIEW projectdata.business_list AS
>  SELECT t.item_id, t.business, t.comments
>    FROM projectdata.data_business_list t;
>
> CREATE OR REPLACE RULE update_buslistview AS
>     ON UPDATE TO projectdata.business_list DO INSTEAD  UPDATE
> projectdata.data_business_list
> SET item_id = new.item_id, business = new.business, comments = new.comments
>   WHERE data_business_list.item_id = old.item_id;

What is the item_id going to get set to by the update up there?  I'm
thinking with that query, item_id will be null.

> We've tried several variations of the update rule: with and without the
> item_id, etc. The 'comments' column does update correctly through the view.

Have you tried this:

UPDATE projectdata.business_list SET business = 13, item_id=item_id
where item_id = 1 and
business = 7;