From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200208262135.g7QLZKn21408@candle.pha.pa.us>
Subject: Re: Efficient DELETE Strategies
In-Reply-To: <j8u9gukf7882nq3tsfhqr5bte9386p637l@4ax.com>
To: Manfred Koizar <mkoi-pg@aon.at>
Date: Mon, 26 Aug 2002 17:35:20 -0400 (EDT)
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Christoph Haller <ch@rodos.fzk.de>,
	pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


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, Pennsylvania 19073