Обсуждение: table alias

I'm running a binary snapshot from two days ago.  When I run a update with
aliases the SQL doesn't work.  The alias does work if I run just a select
statement however.  Is this suppose to be like this.  If not is it only
broken in the 7.5 WIN32 build?

When I try an update like the one below I get the error that follows.

update report_base s
set s.sql_base = 'select * from test_data where'
where s.report_base_id = 1;

ERROR:  syntax error at or near "s" at character 20

The SQL with the aliase taken out works fine as seen below.
update report_base
set sql_base = 'select * from test_data where'
where report_base_id = 1;

Query returned successfully: 1 rows affected, 282 ms execution time.

Thanks,
Bob








*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information.  If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited.  If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies. 
*************************************************************************

> I'm running a binary snapshot from two days ago.  When I run a update
with
> aliases the SQL doesn't work.  The alias does work if I run just a
select
> statement however.  Is this suppose to be like this.  If not is it
only
> broken in the 7.5 WIN32 build?

Look at the grammar for select and update (\h update, \h select) in
psql.  You will notice that select has grammar for alias, update does
not

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] )
] ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias
[, ...] | column_definition

there is no reason to alias the update statement because you can only
specify one table, so it is not really necessary to prefix each column
with the table name (and thus no reason for an alias).  I didn't check,
but the unix version almost certainly works the same

Merlin

The reason I brought this up is I come from an Oracle background and had
used aliases in all kinds of statements.  For Oracle Development I also use
a SQL editor  that has auto complete so when I alias a table and than put a
period after the alias it lists all the columns for that table.  So out of
habit I alias everything.

This makes me type much less when writing SQL scripts all day.  It comes in
handy even when you are doing an update which is only one table but have to
update 20+ columns.  Mainly me being lazy, but hey what good are computers
for if a guy can't be a little lazy and still accomplish his goals.
Definitely not a feature I need to be happy.

Thanks for the info



|---------+------------------------------>
|         |           "Merlin Moncure"   |
|         |           <merlin.moncure@rcs|
|         |           online.com>        |
|         |                              |
|         |           05/25/2004 09:20 AM|
|         |                              |
|---------+------------------------------>

>------------------------------------------------------------------------------------------------------------------------------|
  |
        | 
  |       To:       <Bob.Henkel@hartfordlife.com>
        | 
  |       cc:       <pgsql-hackers-win32@postgresql.org>
        | 
  |       Subject:  RE: [pgsql-hackers-win32] table alias
        | 

>------------------------------------------------------------------------------------------------------------------------------|




> I'm running a binary snapshot from two days ago.  When I run a update
with
> aliases the SQL doesn't work.  The alias does work if I run just a
select
> statement however.  Is this suppose to be like this.  If not is it
only
> broken in the 7.5 WIN32 build?

Look at the grammar for select and update (\h update, \h select) in
psql.  You will notice that select has grammar for alias, update does
not

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] )
] ]
    ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias
[, ...] | column_definition

there is no reason to alias the update statement because you can only
specify one table, so it is not really necessary to prefix each column
with the table name (and thus no reason for an alias).  I didn't check,
but the unix version almost certainly works the same

Merlin







*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may
containproprietary, confidential and/or privileged information.  If you are not the intended recipient, any use,
copying,disclosure, dissemination or distribution is strictly prohibited.  If you are not the intended recipient,
pleasenotify the sender immediately by return e-mail, delete this communication and destroy all copies. 
*************************************************************************

Bob.Henkel@hartfordlife.com writes:
> When I try an update like the one below I get the error that follows.

> update report_base s
> set s.sql_base = 'select * from test_data where'
> where s.report_base_id = 1;

> ERROR:  syntax error at or near "s" at character 20

This is correct per SQL standard.  There are some other databases that
allow an alias for the UPDATE target, but not Postgres.

            regards, tom lane