Re: Efficient DELETE Strategies

On Wed, 12 Jun 2002 10:49:26 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:
>Manfred Koizar <mkoi-pg(at)aon(dot)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 lineitem
USING order o, lineitem l
WHERE o.qty < 1 AND o.order_num = l.order_num

But what would they do on

DELETE FROM lineitem
USING lineitem l1, lineitem l2
WHERE l1.item_num < l2.item_num
AND l1.order_num = l2.order_num

Servus
Manfred