Re: Efficient DELETE Strategies
От | Bruce Momjian |
---|---|
Тема | Re: Efficient DELETE Strategies |
Дата | |
Msg-id | 200208262135.g7QLZKn21408@candle.pha.pa.us обсуждение исходный текст |
Ответ на | Re: Efficient DELETE Strategies (Manfred Koizar <mkoi-pg@aon.at>) |
Список | pgsql-sql |
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, Pennsylvania19073
В списке pgsql-sql по дате отправления: