The following bug has been logged online:

Bug reference: 2789
Logged by: Lucian Capdefier
Email address: luciancapdefier (AT) gmail (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, Nov 28, 2006 at 00:21:27 +0000,
Lucian Capdefier <luciancapdefier (AT) gmail (DOT) com> wrote:
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.

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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

------=_Part_40806_18313654.1165184100884
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 (AT) wolff (DOT) to> wrote:
------=_Part_40806_18313654.1165184100884
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Thanks for the info. Probably I misread the documentation of the Delete clause.<br><br><br>Again thank you,<br>Lucian<br><br><div><span class="gmail_quote">On 12/3/06, <b class="gmail_sendername">Bruno Wolff III</b> &lt;<a href="mailto:bruno (AT) wolff (DOT) to">
bruno (AT) wolff (DOT) to</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">On Tue, Nov 28, 2006 at 00:21:27 +0000,<br>&nbsp;&nbsp;Lucian Capdefier &lt;
<a href="mailto:luciancapdefier (AT) gmail (DOT) com">luciancapdefier (AT) gmail (DOT) com</a>&gt; wrote:<br>&gt;<br>&gt; 1. I cannot user table alias in the FROM clause of the DELETE statement.<br><br>That isn't in the SQL spec, but the extension to allow it has been added
<br>to 8.2 which will be released very shortly.<br><br>&gt; 2. I cannot user more than one, table in the FROM clause.<br><br>The FROM clause lists the (single) table that rows are to be deleted from.<br>If you want to join to other tables you should use the USING clause.
<br>USING was added a release or two ago, so if you have a really old release,<br>if might not be available there. The solution in that case is to take<br>advantage of the &quot;missing from&quot; feature and just use the table without
<br>giving the table name in a FROM or USING clause.<br><br>For questions like this you are going to be better off asking on the<br>general or novice lists instead of bugs. Less people are going to see<br>the message on the bugs list, so you are less likely to get help with
<br>posts there.<br></blockquote></div><br>

------=_Part_40806_18313654.1165184100884--