Re: DELETE syntax on JOINS

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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 once

test=> DELETE FROM test t USING test t;
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +