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

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