Re: Efficient DELETE Strategies

Manfred Koizar <mkoi-pg(at)aon(dot)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