Re: DELETE syntax on JOINS

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 2009/8/24 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> ... Some other systems
>> allow it by letting you re-specify the target in the other clause,
>> equivalently to
>>
>> DELETE FROM target t USING t LEFT JOIN other_table ot ON ...
>>
>> but we have always considered that the target is *not* to be identified
>> with any member of the FROM/USING clause, so it would be a serious
>> compatibility break to change that now.

> I'm all in favor of compatibility, but if there is any way to make
> this work without massive collateral damage, I am also all in favor of
> that. I am forever writing queries that contain a needless self-join
> to work around the impossibility of directly outer-joining against the
> target.

It'd be pretty easy to do if we were willing to introduce a new reserved
word; for example

DELETE FROM target t USING SELF LEFT JOIN other_table ot ON ...

(or maybe TARGET instead of SELF, or some other word). Wouldn't do
anything for exact compatibility with MySQL or anybody else using the
respecify-the-target-table-name approach. But it would be unambiguous
and backwards-compatible. The real problem with this is that all the
good candidates for the reserved word are things people are probably
already using as aliases, so we'd have a large risk of breaking existing
queries. We could avoid that with a sufficiently ugly choice like

DELETE FROM target t USING DELETE_TARGET LEFT JOIN other_table ot ON ...

but yech ...

regards, tom lane