Re: funny update, say update 1, changed 2 records.

On Sat, 2005-06-11 at 13:29 -0400, Tom Lane wrote:
> Neil Dugan <postgres(at)butterflystitches(dot)com(dot)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