| 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 |
