Обсуждение: BUG #2789: problem with delete statement

The following bug has been logged online:

Bug reference:      2789
Logged by:          Lucian Capdefier
Email address:      luciancapdefier@gmail.com
PostgreSQL version: 8.1.5
Operating system:   Windows XP
Description:        problem with delete statement
Details:

I have noticed a problem with the DELETE statement.


1. I cannot user table alias in the FROM clause of the DELETE statement.
2. I cannot user more than one, table in the FROM clause.

Example:

1.
delete
  from fxkanban.product_offer
 where offer_id=1 and
       exists (select 1 from fxkanban.product_offer x, fxkanban.product p
                        where x.product_id=p.product_id and
                              x.offer_id=offer_id and
                              p.owner='test')
works ok



delete
  from fxkanban.product_offer o
 where o.offer_id=1 and
       exists (select 1 from fxkanban.product_offer x, fxkanban.product p
                        where x.product_id=p.product_id and
                              x.offer_id=o.offer_id and
                              p.owner='test')
does not work





2.
delete
  from only fxkanban.product_offer, fxkanban.product p
 where o.offer_id=1 and
       o.product_id=p.product_id and
       p.owner='test'
does not work


Both problems issue SQL Error 42601

On Tue, Nov 28, 2006 at 00:21:27 +0000,
  Lucian Capdefier <luciancapdefier@gmail.com> wrote:
>
> 1. I cannot user table alias in the FROM clause of the DELETE statement.

That isn't in the SQL spec, but the extension to allow it has been added
to 8.2 which will be released very shortly.

> 2. I cannot user more than one, table in the FROM clause.

The FROM clause lists the (single) table that rows are to be deleted from.
If you want to join to other tables you should use the USING clause.
USING was added a release or two ago, so if you have a really old release,
if might not be available there. The solution in that case is to take
advantage of the "missing from" feature and just use the table without
giving the table name in a FROM or USING clause.

For questions like this you are going to be better off asking on the
general or novice lists instead of bugs. Less people are going to see
the message on the bugs list, so you are less likely to get help with
posts there.

Thanks for the info. Probably I misread the documentation of the Delete
clause.


Again thank you,
Lucian

On 12/3/06, Bruno Wolff III <bruno@wolff.to> wrote:
>
> On Tue, Nov 28, 2006 at 00:21:27 +0000,
>   Lucian Capdefier <luciancapdefier@gmail.com> wrote:
> >
> > 1. I cannot user table alias in the FROM clause of the DELETE statement.
>
> That isn't in the SQL spec, but the extension to allow it has been added
> to 8.2 which will be released very shortly.
>
> > 2. I cannot user more than one, table in the FROM clause.
>
> The FROM clause lists the (single) table that rows are to be deleted from.
> If you want to join to other tables you should use the USING clause.
> USING was added a release or two ago, so if you have a really old release,
> if might not be available there. The solution in that case is to take
> advantage of the "missing from" feature and just use the table without
> giving the table name in a FROM or USING clause.
>
> For questions like this you are going to be better off asking on the
> general or novice lists instead of bugs. Less people are going to see
> the message on the bugs list, so you are less likely to get help with
> posts there.
>