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(at)apt-cafm(dot)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;