Re: Efficient DELETE Strategies

On Mon, 10 Jun 2002 09:56:27 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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