Обсуждение: Table aliases in delete statements?

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

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

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Keith Parks <emkxp01@mtcc.demon.co.uk> writes:
> Is there any reason for not allowing table aliases in
> delete statements?

Not much, I suppose, but it's not in SQL92:
        <delete statement: searched> ::=             DELETE FROM <table name>               [ WHERE <search condition>
]

The expansion of <table name> doesn't mention anything about aliases.

As Bruce points out in another followup, there's no real need for
an alias for the target table; if you have sub-selects that need
independent references to the target, you can always alias *them*.
The same goes for INSERT and UPDATE, which also take unadorned
<table name> as the target table specification.
        regards, tom lane

Then <tgl@sss.pgh.pa.us> spoke up and said:
> Keith Parks <emkxp01@mtcc.demon.co.uk> writes:
> > Is there any reason for not allowing table aliases in
> > delete statements?
> 
> As Bruce points out in another followup, there's no real need for
> an alias for the target table; if you have sub-selects that need
> independent references to the target, you can always alias *them*.
> The same goes for INSERT and UPDATE, which also take unadorned
> <table name> as the target table specification.

Unless your query is going to be long enough to run into query length
limits, aliases are not your friends.  Standard SQL they may be, but
aliases always end up obscuring queries to those who come along after
you. 

-- 
=====================================================================
| JAVA must have been developed in the wilds of West Virginia.      |
| After all, why else would it support only single inheritance??    |
=====================================================================
| Finger geek@cmu.edu for my public key.                            |
=====================================================================