Table aliases in delete statements? - Mailing list pgsql-hackers

From Keith Parks
Subject Table aliases in delete statements?
Date
Msg-id 199912072238.WAA08259@mtcc.demon.co.uk
Whole thread Raw
Responses Re: [HACKERS] Table aliases in delete statements?
Re: [HACKERS] Table aliases in delete statements?
List pgsql-hackers
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=> 



pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and
Next
From: Tom Lane
Date:
Subject: Re: [HACKERS] Parallel regress tests (was Re: FOREIGN KEY and