Postgres Pro
Downloads
Home   >   mailing lists

Thread: ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

From
m laks
Date:
Hi,
I am migrating to postgresql 8.1 from 7.4 with debian.

Now I notice that one of my perl DBI scripts is crashing with the error above, and worked fine before.

From google I learn that postgresql is  getting tougher on us malfeasant coders who violate the SQL rules :(.

 I have not slept in two days :(.

I am unable to do a delete from a table  where the condition is that of a join.

I am ignorant on how to do it correctly.

the following worked before:

LTA_IDB=# delete  from instancetable where ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid));        (at least in postgresql 7.4 using perl DBI).

It worked on postgresql 7.4 and now on postgresql 8.1 I get error:

ERROR:  missing FROM-clause entry for table "imagelevel"

(Here we want to drop those entries in instancetable whose imageuid agrees with the imagelevel entries with serpatient 1.2.840.113704.1.111.4640.1185891989.4.
(these are CT scan and MRI DICOM medical images)).

Now this following select works:

select * from instancetable, imagelevel where ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid));

without error in psql.

Then how to drop them?

(Now I actually want to drop those corrsponding entries in both instancetable and imagelevel) 

So I tried stuff like this: with failure: :(


LTA_IDB=# delete from select * from instancetable, imagelevel where ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid));
ERROR:  syntax error at or near "select" at character 13
LINE 1: delete from select * from instancetable, imagelevel where ( ...

LTA_IDB=# delete from select * from instancetable, imagelevel where ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid));
ERROR:  syntax error at or near "select" at character 13
LINE 1: delete from select * from instancetable, imagelevel where ( ...
                    ^
LTA_IDB=# delete from (select * from instancetable, imagelevel where ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid)));
ERROR:  syntax error at or near "(" at character 13
LINE 1: delete from (select * from instancetable, imagelevel where (...
                    ^
LTA_IDB=# delete from instancetable  (select * from instancetable, imagelevel where ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid)));
ERROR:  syntax error at or near "(" at character 28
LINE 1: delete from instancetable  (select * from instancetable, ima...
                                   ^
LTA_IDB=# delete from instancetable  select * from instancetable, imagelevel where ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid));
ERROR:  syntax error at or near "select" at character 28
LINE 1: delete from instancetable  select * from instancetable, imag...


I tried reading around... .. I thank you deeply!

Thanks,

Mitchell Laks



Re: ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

From
"Jaime Casanova"
Date:
On Tue, Jun 17, 2008 at 1:25 AM, m laks <mlaks2000@yahoo.com> wrote:
> Hi,
> I am migrating to postgresql 8.1 from 7.4 with debian.
>
[...]
>
> the following worked before:
>
> LTA_IDB=# delete  from instancetable where ( (imagelevel.serparent=
> '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid=
> imagelevel.sopinsuid));        (at least in postgresql 7.4 using perl DBI).
>
> It worked on postgresql 7.4 and now on postgresql 8.1 I get error:
>
> ERROR:  missing FROM-clause entry for table "imagelevel"
>

maybe the add_missing_from parameter in postgresql.conf is what you
need to get some sleep :)
it's there for older application like yours


--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Guayaquil - Ecuador
Cel. (593) 87171157

Re: ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

From
Tommy Gildseth
Date:
m laks wrote:
> Hi,
> I am migrating to postgresql 8.1 from 7.4 with debian.
>
> Now I notice that one of my perl DBI scripts is crashing with the error
> above, and worked fine before.
>
> LTA_IDB=# delete  from instancetable where ( (imagelevel.serparent=
> '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid=
> imagelevel.sopinsuid));        (at least in postgresql 7.4 using perl DBI).
>
> It worked on postgresql 7.4 and now on postgresql 8.1 I get error:
>
> ERROR:  missing FROM-clause entry for table "imagelevel"



How about something along these lines:


delete from instancetable
USING imagelevel
WHERE (
   (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and
   (instancetable.imageuid= imagelevel.sopinsuid)
);

--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39

Re: ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

From
m laks
Date:
--- On Tue, 6/17/08, Tommy Gildseth <tommy.gildseth@usit.uio.no> wrote:
F


How about something along these lines:


delete from instancetable
USING imagelevel
WHERE (
   (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and
   (instancetable.imageuid= imagelevel.sopinsuid)
)

Thank You so much!! It works perfectly!!!!!

1. Where is this documented, and 
2. where can I read more about what changed
between the 7 series and 8 series that caused this?

Thank you so much!!!!!!!!!
Mitchell

Re: ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

From
m laks
Date:
--- On Tue, 6/17/08, Jaime Casanova <systemguards@gmail.com> wrote:

maybe the add_missing_from parameter in postgresql.conf is what you
need to get some sleep :)
it's there for older application like yours


Dear Jaime,

Tommy's suggestion worked right away. I wanted to 
understand more of the philiosphy behind what you wrote.

Looking around, I just found many references to changes like this
 
Now that DELETE has a USING clause, we should be able to make add_missing_from=false the default in 8.1; the attached patch implements this and updates the documentation. Barring any objections, I'll apply this tomorrow. 
 Is there a canonical place to read more about this issue?

Thank you all so much!!!!!
Mitchell

Re: ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

From
Tommy Gildseth
Date:
m laks wrote:
> --- On *Tue, 6/17/08, Tommy Gildseth /<tommy.gildseth@usit.uio.no>/* wrote:
>
>     F
>
>
>
>     How about something along these lines:
>
>
>     delete from instancetable
>     USING imagelevel
>     WHERE (
>        (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and
>        (instancetable.imageuid= imagelevel.sopinsuid)
>     )
>
>     Thank You so much!! It works perfectly!!!!!
>
>     1. Where is this documented, and
>     2. where can I read more about what changed
>     between the 7 series and 8 series that caused this?
>
>     Thank you so much!!!!!!!!!
>     Mitchell
>
>


As usual, the fine manual :-)
http://www.postgresql.org/docs/8.1/interactive/sql-delete.html

--
Tommy Gildseth

Re: ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join

From
"Richard Broersma"
Date:
On Tue, Jun 17, 2008 at 5:03 AM, m laks <mlaks2000@yahoo.com> wrote:
> --- On Tue, 6/17/08, Tommy Gildseth <tommy.gildseth@usit.uio.no> wrote:

> 1. Where is this documented, and
> 2. where can I read more about what changed
> between the 7 series and 8 series that caused this?

http://www.postgresql.org/docs/8.0/interactive/release-8-0.html
http://www.postgresql.org/docs/8.1/interactive/release-8-1.html


--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug