Re: Efficient DELETE Strategies - Mailing list pgsql-sql

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@sss.pgh.pa.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@postgresql.org)
> 

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