Re: DELETE syntax on JOINS

Robert Haas <robertmhaas@gmail.com> writes:
> 2009/8/24 Tom Lane <tgl@sss.pgh.pa.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