Keith Parks <emkxp01@mtcc.demon.co.uk>
>>Bruce Momjian <pgman@candle.pha.pa.us>
>
>>
>>> emkxp01=> delete from deltest s1 where s1.respdate not in ( select
>>> min(s2.respdate) from deltest s2 where s1.sessionid = s2.sessionid);
>>> ERROR: parser: parse error at or near "s1"
>>> emkxp01=>
>>
>>Don't use s1. Just refer to native deltest in the subquery. That
>>should reference the outer table.
>
>That doesn't seem to work as 3 rows are deleted and not just the
>two duplicates.
>
>emkxp01=> delete from deltest where respdate not in ( select min(s2.respdate)
>from deltest s2 where sessionid = s2.sessionid);
>DELETE 3
>emkxp01=> select * from deltest;
> sessionid | respdate
>-----------+------------------------------
> 1 | Tue 07 Dec 22:32:01 1999 GMT
>(1 row)
>
>emkxp01=>
Ooops sorry, it does work if I use the tablename.colname syntax.
emkxp01=> delete from deltest where respdate not in ( select min(s2.respdate)
from deltest s2 where deltest.sessionid = s2.sessionid);
DELETE 2
emkxp01=> select * from deltest; sessionid | respdate
-----------+------------------------------ 1 | Tue 07 Dec 22:32:01 1999 GMT 2 | Wed 08 Dec 00:48:59 1999
GMT
(2 rows)
emkxp01=>