Thread

Re: [HACKERS] Table aliases in delete statements?

Keith Parks <emkxp01@mtcc.demon.co.uk> — 1999-12-08T00:52:03Z

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