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
Date: Fri, 7 Jun 2002 09:38:28 -0700 (PDT)
Message-ID: <20020607093629.Y37907-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <200206071133.23179.dev@archonet.com>
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).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, dev@archonet.com
Subject: Re: UPDATE ... FROM vs standard SQL
In-Reply-To: <20020607093629.Y37907-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox