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