Re: DELETE syntax on JOINS

Greg Stark <gsstark(at)mit(dot)edu> writes:
> You would have to write something like

> UPDATE foo set a = (select aa from bar where...)
> b = (select bb from bar where...)

> and then the optimizer would have to notice the duplicates and
> consolidate them? That seems inconvenient (and fragile).

Well, that's why the spec nowadays allows you to write

UPDATE foo SET (a,b) = (select aa,bb from bar where ...)

But we haven't got that, and if we did it would generate a nestloop
plan. Getting to the point of absolute performance equivalence between
subqueries and joins would take a *lot* of work; I'm not even sure it's
possible at all. And once we'd done all that work there would still
remain the fact that people are accustomed to using join syntax instead.
There's a lot of existing code out there that would be a lot easier
to port to PG if we supported that style (which was exactly the point
made by the OP).

regards, tom lane