Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table alias in DELETE statements

Table alias in DELETE statements

From
Hanno Wiegard
Date:
Hi,

I've already checked the mailing archive but found nothing about
problems with the DELETE statement and table alias but it's not
mentioned in the docs. So the question for me is whether it is possible
to use a table alias in a DELETE statement or not, e.g. 
DELETE FROM foo f WHERE f.ID > 3000 (more complicated cases in reality 
and I really need the alias because the SQL is generated automaically 
by a tool)?

Thanks,
Hanno Wiegard



______________________________________________________________________________
Die clevere Geldreserve: der DiBa-Privatkredit. Funktioniert wie ein Dispo, 
ist aber viel gunstiger! Alle Infos: http://diba.web.de/?mc=021104

Re: Table alias in DELETE statements

From
Manfred Koizar
Date:
On Fri, 13 Sep 2002 14:10:25 +0200, Hanno Wiegard <hwiegard@web.de>
wrote:
>So the question for me is whether it is possible
>to use a table alias in a DELETE statement or not, e.g. 
>DELETE FROM foo f WHERE f.ID > 3000 (more complicated cases in reality 

Hanno, looks like you are out of luck here.

PG 7.3: DELETE FROM [ ONLY ] table [ WHERE condition ]

SQL92: DELETE FROM <table name> [ WHERE <search condition> ]

SQL99: DELETE FROM <target table> [ WHERE <search condition> ] <target table> ::= [ ONLY ] <left paren> <table name>
<rightparen>
 

which BTW makes "DELETE FROM mytable" invalid.  This would have to be
"DELETE FROM (mytable)".  Is there something wrong with my copy of the
standard?

There has been a discussion on -hackers about extensions to the DELETE
statement (DELETE [FROM] a FROM b, c WHERE ...).  If this gets
implemented in a future release, there's a certain chance for a table
alias.

>and I really need the alias because the SQL is generated automaically 
>by a tool)?

... and this tool works with what database?

ServusManfred

Re: Table alias in DELETE statements

From
Christopher Kings-Lynne
Date:
On the several occasions I've tried using table aliases in delete
statements, it hasn't worked.

Chris

On Fri, 13 Sep 2002, Hanno Wiegard wrote:

> Hi,
>
> I've already checked the mailing archive but found nothing about
> problems with the DELETE statement and table alias but it's not
> mentioned in the docs. So the question for me is whether it is possible
> to use a table alias in a DELETE statement or not, e.g.
> DELETE FROM foo f WHERE f.ID > 3000 (more complicated cases in reality
> and I really need the alias because the SQL is generated automaically
> by a tool)?
>
> Thanks,
> Hanno Wiegard
>
>
>
> ______________________________________________________________________________
> Die clevere Geldreserve: der DiBa-Privatkredit. Funktioniert wie ein Dispo,
> ist aber viel gunstiger! Alle Infos: http://diba.web.de/?mc=021104
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: Table alias in DELETE statements

From
Bruce Momjian
Date:
TODO has:
* Allow DELETE to handle table aliases for self-joins [delete]

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> On the several occasions I've tried using table aliases in delete
> statements, it hasn't worked.
> 
> Chris
> 
> On Fri, 13 Sep 2002, Hanno Wiegard wrote:
> 
> > Hi,
> >
> > I've already checked the mailing archive but found nothing about
> > problems with the DELETE statement and table alias but it's not
> > mentioned in the docs. So the question for me is whether it is possible
> > to use a table alias in a DELETE statement or not, e.g.
> > DELETE FROM foo f WHERE f.ID > 3000 (more complicated cases in reality
> > and I really need the alias because the SQL is generated automaically
> > by a tool)?
> >
> > Thanks,
> > Hanno Wiegard
> >
> >
> >
> > ______________________________________________________________________________
> > Die clevere Geldreserve: der DiBa-Privatkredit. Funktioniert wie ein Dispo,
> > ist aber viel gunstiger! Alle Infos: http://diba.web.de/?mc=021104
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073