Monday, June 16, 2008

[GENERAL] 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



No comments: