UPDATE ... FROM vs standard SQL

From: Richard Huxton <dev(at)archonet(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: UPDATE ... FROM vs standard SQL
Date: 2002-06-07 10:33:23
Message-ID: 200206071133.23179.dev@archonet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Am I missing something here?
TIA

- Richard Huxton

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Achilleus Mantzios 2002-06-07 11:23:33 Re: arrays as pgsql function parameters
Previous Message Bruce Momjian 2002-06-07 04:19:36 Re: PL/pgSQL TODO