Re: DELETE syntax on JOINS

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Bruce Momjian wrote:
> >> So the problem is that our DELETE ... USING does not allow ANSI join
> >> syntax?  Can that be added?
> 
> > Not sure about that.  USING is already an extension to the standard, so
> > if we extend it a bit more, it can't be a problem, can it?
> 
> I don't see any very good way to extend the USING syntax to allow the
> target table to be outer-joined to something else.  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.

Let's look at this a little closer.  We can use an alias in the DELETE
FROM clause:
test=> DELETE FROM test t;test=> DELETE FROM test t USING test;

What we cannot currently do is reference test twice:
test=> DELETE FROM test USING test;ERROR:  table name "test" specified more than oncetest=> DELETE FROM test t USING
testt;ERROR:  table name "t" specified more than once
 

As far as I understand it, allowing ANSI joins in USING would simple
mean removing that error message and linking the two table aliases.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +