Обсуждение: ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join
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
От
"Jaime Casanova"
Дата:
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
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
--- On Tue, 6/17/08, Tommy Gildseth <tommy.gildseth@usit.uio.no> wrote:F |
--- On Tue, 6/17/08, Jaime Casanova <systemguards@gmail.com> wrote:
|
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
От
"Richard Broersma"
Дата:
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