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
