Re: [HACKERS] Table aliases in delete statements?

Keith Parks <emkxp01(at)mtcc(dot)demon(dot)co(dot)uk>
>>Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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=>