From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Christoph Haller <ch(at)rodos(dot)fzk(dot)de>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Efficient DELETE Strategies |
Date: | 2002-06-12 18:06:11 |
Message-ID: | dgqeguc0kf8ord0g37vo3hm6maqk649jak@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2002-06-12 20:32:05 | Re: General trigger function |
Previous Message | Bruce Momjian | 2002-06-12 17:52:00 | Re: Domains |