Thread

Table aliases in delete statements?

Keith Parks <emkxp01@mtcc.demon.co.uk> — 1999-12-07T22:38:08Z

Hi All,

Is there any reason for not allowing table aliases in
delete statements?

I was trying to delete duplicates from an ascend log
database when I hit the following "parse" error.

(Perhaps I shouldn't be using a correlated subquery!!)

Simplified example follows.....


emkxp01=> create table deltest ( sessionid int, respdate datetime );
CREATE
emkxp01=> insert into deltest values ( 1, now() );
INSERT 58395 1
emkxp01=> insert into deltest values ( 1, now() );
INSERT 58396 1
emkxp01=> insert into deltest values ( 2, now() );
INSERT 58397 1
emkxp01=> insert into deltest values ( 2, now() );
INSERT 58398 1
emkxp01=> select * from deltest s1 where s1.respdate not in ( select 
min(s2.respdate) from deltest s2 where s1.sessionid = s2.sessionid);
 sessionid |           respdate           
-----------+------------------------------
         1 | Tue 07 Dec 22:32:08 1999 GMT
         2 | Tue 07 Dec 22:32:19 1999 GMT
(2 rows)

emkxp01=> select * from deltest;                                                                                                         
 sessionid |           respdate           
-----------+------------------------------
         1 | Tue 07 Dec 22:32:01 1999 GMT
         1 | Tue 07 Dec 22:32:08 1999 GMT
         2 | Tue 07 Dec 22:32:14 1999 GMT
         2 | Tue 07 Dec 22:32:19 1999 GMT
(4 rows)

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