Обсуждение: 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. >