To: Manfred Koizar <mkoi-pg@aon.at>
Cc: Christoph Haller <ch@rodos.fzk.de>, pgsql-sql@postgresql.org,
	pgsql-hackers@postgresql.org
Subject: Re: Efficient DELETE Strategies 
In-reply-to: <j8u9gukf7882nq3tsfhqr5bte9386p637l@4ax.com> 
References: <200206101142.NAA16854@rodos> <5619.1023717387@sss.pgh.pa.us>
	<j8u9gukf7882nq3tsfhqr5bte9386p637l@4ax.com>
Comments: In-reply-to Manfred Koizar <mkoi-pg@aon.at>
	message dated "Mon, 10 Jun 2002 22:23:38 +0200"
Date: Mon, 10 Jun 2002 17:07:56 -0400
Message-ID: <8806.1023743276@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Manfred Koizar <mkoi-pg@aon.at> writes:
>> If so, what's their syntax?

> MSSQL seems to guess what the user wants.

Gack.  Nothing like treating mindless syntax variations as a "feature"
list...

> All the following statements do the same:

> (1)  DELETE t1 FROM t2 WHERE t1.i=t2.i
> (2a) DELETE t1 FROM t2, t1 WHERE t1.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)

So in other words, MSSQL has no idea whether the name following DELETE
is a real table name or an alias, and it's also unclear whether the name
appears in the separate FROM clause or generates a FROM-item all by
itself.  This is why they have to punt on these cases:

> 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."

The ambiguity is entirely self-inflicted...

> 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" ...

Hm.  So (1) with the DELETE FROM corresponds exactly to what I was
suggesting:
	DELETE FROM t1 FROM t2 WHERE t1.i=t2.i
except that I'd also allow an alias in there:
	DELETE FROM t1 a FROM t2 b WHERE a.i=b.i

Given the plethora of mutually incompatible interpretations that MSSQL
evidently supports, though, I fear we can't use it as precedent for
making any choices :-(.

Can anyone check out other systems?

			regards, tom lane