Re: Efficient DELETE Strategies
От | Manfred Koizar |
---|---|
Тема | Re: Efficient DELETE Strategies |
Дата | |
Msg-id | dgqeguc0kf8ord0g37vo3hm6maqk649jak@4ax.com обсуждение исходный текст |
Ответ на | Re: Efficient DELETE Strategies (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-sql |
On Wed, 12 Jun 2002 10:49:26 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: >Manfred Koizar <mkoi-pg@aon.at> writes: >> Oracle basically supports (with slight variations between releases >> 7/8/9): >> DELETE [FROM] { table >> | view >> | ( subquery ) >> } >> [alias] [WHERE ...] [returning_clause] > >Bizarre. How are you supposed to delete from a subquery? Hey, don't blame *me* :-) The thought seems to be, if it is ok to delete from a view, and a view is just a name for a query, why not allow to delete from a query. Here is an example out of the reference manual:DELETE FROM (select * from emp)WHERE JOB = 'SALESMAN'AND COMM < 100; To be clear: I do *not* think, we need this in PostgreSQL. Otherwise we'd also have to support delete from the result set of a function ;-) BTW, I did some more digging. The results are somewhat confusing. O7: no subquery O8 v8.0: subquery allowed O8i v8.1.5: DELETE [ FROM ] table_expression_clause [ where_clause ] table_expression_clause ::= { schema . { table | view | snapshot } | ( subquery) | table_collection_expression } [ , ... ] Note, the syntax diagram in the "Oracle8i SQL Reference" claims, that table_expression_clause can contain more than one table, view, etc. but this feature(?) is not mentioned in the text. Please, could someone try this? O9i: only one table, view, ...DELETE [hint] [FROM] { dml_table_expression_clause| ONLY ( dml_table_expression_clause ) }[t_alias][where_clause] [returning_clause]; dml_table_expression_clause ::={ [schema .] { table [ { PARTITION ( partition ) | SUBPARTITION ( subpartition )} | @ dblink ] | { view | materialized view } [@ dblink] } | ( subquery [subquery_restriction_clause] )| table_collection_expression} One more thing I found: Informix XPS (Extended Parallel Server) v8.3 and later allows DELETE FROM { table | view | synonym } [ { USING | FROM } { table | view | synonym | alias } [ , ...] ] [ WHERE condition ] which looks pretty much like your suggestion. Though the semantics are a bit fuzzy. They require the target table to be listed after the USING (or second FROM) keyword and give this example: DELETE FROM lineitemUSING order o, lineitem lWHERE o.qty < 1 AND o.order_num = l.order_num But what would they do on DELETE FROM lineitemUSING lineitem l1, lineitem l2WHERE l1.item_num < l2.item_num AND l1.order_num = l2.order_num ServusManfred
В списке pgsql-sql по дате отправления: