Thread

1. BUG #2789: problem with delete statement

   Lucian Capdefier <luciancapdefier@gmail.com> — 2006-11-28T00:21:27Z

   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
   
   

2. Re: BUG #2789: problem with delete statement

   Bruno Wolff III <bruno@wolff.to> — 2006-12-03T15:27:19Z

   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.
   
   

3. Re: BUG #2789: problem with delete statement

   Lucian Capdefier <luciancapdefier@gmail.com> — 2006-12-03T22:15:00Z

   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.
   >