Re: UPDATE ... FROM vs standard SQL

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).