Re: Efficient DELETE Strategies

Added to TODO:

* Allow DELETE to handle table aliases for self-joins [delete]

---------------------------------------------------------------------------

Manfred Koizar wrote:
> On Mon, 10 Jun 2002 09:56:27 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> wrote:
> >Does anyone know whether other systems that support the UPDATE extension
> >for multiple tables also support a DELETE extension for multiple tables?
> >If so, what's their syntax?
>
> MSSQL seems to guess what the user wants. All the following
> statements do the same:
>
> (0) DELETE FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.i=t2.i)
> (1) DELETE t1 FROM t2 WHERE t1.i=t2.i
> (2a) DELETE t1 FROM t2, t1 WHERE t1.i=t2.i
> (2b) DELETE t1 FROM t2 INNER JOIN t1 ON t1.i=t2.i
> (3a) DELETE t1 FROM t2, t1 a WHERE a.i=t2.i
> (3b) DELETE t1 FROM t2 INNER JOIN t1 a ON a.i=t2.i
> (4a) DELETE a FROM t2, t1 a WHERE a.i=t2.i
> (4b) DELETE a FROM t2 INNER JOIN t1 a ON a.i=t2.i
> (5) DELETE t1 FROM t1 a
> WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i)
> (6) DELETE a FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i=t2.i)
>
> (0) is standard SQL and should always work. As an extension I'd like
> (1) or (2), but only one of them and forbid the other one. I'd also
> forbid (3), don't know what to think of (4), and don't see a reason
> why we would want (5) or (6). I'd rather have (7) or (8).
>
> These don't work:
> (7) DELETE t1 a FROM t2 WHERE a.i = t2.i
> "Incorrect syntax near 'a'."
>
> (8) DELETE FROM t1 a WHERE EXISTS (SELECT * FROM t2 WHERE a.i = t2.i)
> "Incorrect syntax near 'a'."
>
> Self joins:
> (2as) DELETE t1 FROM t1, t1 b WHERE 2*b.i=t1.i
> (4as) DELETE a FROM t1 a, t1 b WHERE 2*b.i=a.i
> (4bs) DELETE a FROM t1 a INNER JOIN t1 b on 2*b.i=a.i
>
> These don't work:
> DELETE t1 FROM t1 b WHERE 2 * b.i = t1.i
> "The column prefix 't1' does not match with a table name or alias name
> used in the query."
>
> DELETE t1 FROM t1 a, t1 b WHERE 2 * b.i = a.i
> "The table 't1' is ambiguous."
>
> And as if there aren't enough ways yet, I just discovered that (1) to
> (6) just as much work with "DELETE FROM" where I wrote "DELETE" ...
>
> Servus
> Manfred
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073