Re: UPDATE ... FROM vs standard SQL

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.
>
> 2. building an updatable view.

If your goal is to gain better portability for your app, go
with the subselect. Updatable views, especially if they do
joins, are far less supported (if at all).

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #