Re: funny update, say update 1, changed 2 records. - Mailing list pgsql-general

From Neil Dugan
Subject Re: funny update, say update 1, changed 2 records.
Date
Msg-id 1118540865.5492.9.camel@localhost.localdomain
Whole thread Raw
In response to Re: funny update, say update 1, changed 2 records.  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
On Sat, 2005-06-11 at 13:29 -0400, Tom Lane wrote:
> Neil Dugan <postgres@butterflystitches.com.au> writes:
> > I have been having some trouble with a particular table view.  An UPDATE
> > command is not only changing the applicable record it is also creating a
> > new record as well.
>
> I think it's because your UPDATE is updating supplier.account_type which
> is part of the join key for the view's underlying join.  Somehow that
> results in the update applying to all supplier rows that join to the
> same account_type row.  Don't have time now to work out exactly why ...
> but the easiest solution is probably to not use a join in the view.
> Instead fetch the account_type.name via a subselect in the view's
> output list.
>
>             regards, tom lane

Hi Tom Lane,

Thanks for the reply, something doesn't sound right here.  How can an
update cause a new record to be created (id = 6) as well as updating the
correct record (id = 3)?

I am not sure what you mean by 'subselect' in the views output list.
Isn't that what I am doing in creating the view?

Regards Neil


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: memory question
Next
From: Tatsuo Ishii
Date:
Subject: Re: PostgreSQL Certification