Re: Efficient DELETE Strategies

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