Date: Fri, 7 Jun 2002 09:38:28 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Richard Huxton <dev@archonet.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: UPDATE ... FROM vs standard SQL
In-Reply-To: <200206071133.23179.dev@archonet.com>
Message-ID: <20020607093629.Y37907-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 7 Jun 2002, Richard Huxton wrote:

> I've been happily using statements like
>   UPDATE a SET flag=1 FROM b WHERE a.id=b.id AND b.foo='x';
>
> While PG's FROM extension makes life simple, I can't believe there's not a way
> to do an update on a join using standard SQL. The two options I can think of
> are:
>
> 1. using a sub-select
>   UPDATE a SET flag=1 WHERE a.id IN (SELECT id FROM b WHERE b.foo='X');
> Which is fine, but no good for mysql, hits PG's speed issue with IN and a bit
> clumsy for more complicated examples.

Well, on PG you might want to try some EXISTS form, but I think this is
the way you're probably intended to do it under SQL92 at least.

> 2. building an updatable view.

Well, AFAICS this is mostly a special case of the above.  It looks to me
that updatable views can't have multiple tables in the from clause so
you'd have to write the select with a subquery anyway (and I'm not 100%
sure that's correct either).