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