pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Something I'd like to try...
7+ messages / 6 participants
[nested] [flat]

* Something I'd like to try...
@ 2000-06-09 11:18 Niall Smart <niall.smart@ebeon.com>
  2000-10-03 03:22 ` Re: Something I'd like to try... Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Niall Smart @ 2000-06-09 11:18 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I just noticed that postgres doesn't totally support
column aliases on UPDATE statements, for example
 
         UPDATE EMPLOYEES SET
                 OFFICE_PHONE = UU.OFFICE_PHONE,
                 MOBILE_PHONE = UU.MOBILE_PHONE,
                 OFFICE_CD = UU.OFFICE_CD,
                 ABOUT_ME = UU.ABOUT_ME
         FROM
                 UNCONFIRMED_UPDATES UU
         WHERE
                 EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND
                 UU.UPDATE_ID = 'HJhjaJ023J19KJAqp'

It is not currently possible to alias EMPLOYEES
so that the test can become

         E.EMPLOYEE_ID = UU.EMPLOYEE_ID

Do the guru's think that this would be hard to add?  Also,
is it desirable?
--

Niall Smart

email:  niall.smart@ebeon.com
phone:  (087) 8052390



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Something I'd like to try...
  2000-06-09 11:18 Something I'd like to try... Niall Smart <niall.smart@ebeon.com>
@ 2000-10-03 03:22 ` Bruce Momjian <pgman@candle.pha.pa.us>
  2000-10-03 04:29   ` Re: Something I'd like to try... Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Bruce Momjian @ 2000-10-03 03:22 UTC (permalink / raw)
  To: Niall Smart <niall.smart@ebeon.com>; +Cc: pgsql-sql

Can someone comment on this?

> Hi,
> 
> I just noticed that postgres doesn't totally support
> column aliases on UPDATE statements, for example
>  
>          UPDATE EMPLOYEES SET
>                  OFFICE_PHONE = UU.OFFICE_PHONE,
>                  MOBILE_PHONE = UU.MOBILE_PHONE,
>                  OFFICE_CD = UU.OFFICE_CD,
>                  ABOUT_ME = UU.ABOUT_ME
>          FROM
>                  UNCONFIRMED_UPDATES UU
>          WHERE
>                  EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND
>                  UU.UPDATE_ID = 'HJhjaJ023J19KJAqp'
> 
> It is not currently possible to alias EMPLOYEES
> so that the test can become
> 
>          E.EMPLOYEE_ID = UU.EMPLOYEE_ID
> 
> Do the guru's think that this would be hard to add?  Also,
> is it desirable?
> --
> 
> Niall Smart
> 
> email:  niall.smart@ebeon.com
> phone:  (087) 8052390
> 


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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Something I'd like to try...
  2000-06-09 11:18 Something I'd like to try... Niall Smart <niall.smart@ebeon.com>
  2000-10-03 03:22 ` Re: Something I'd like to try... Bruce Momjian <pgman@candle.pha.pa.us>
@ 2000-10-03 04:29   ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-10-03 15:41     ` Re: Something I'd like to try... Josh Berkus <josh@agliodbs.com>
  2000-10-03 17:38     ` Re: Something I'd like to try... Barry Lind <barry@xythos.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Tom Lane @ 2000-10-03 04:29 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Niall Smart <niall.smart@ebeon.com>; pgsql-sql

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can someone comment on this?
>> 
>> I just noticed that postgres doesn't totally support
>> column aliases on UPDATE statements, for example

The SQL92 spec very clearly does not allow an alias on the target table:

         13.10  <update statement: searched>

         <update statement: searched> ::=
              UPDATE <table name>
                SET <set clause list>
                [ WHERE <search condition> ]

While I'm willing to consider variations from the spec that add
significant functionality, this proposed addition adds no functionality
worth noticing.  It'd just be another way to trip yourself up when
moving across DBMSes.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Something I'd like to try...
  2000-06-09 11:18 Something I'd like to try... Niall Smart <niall.smart@ebeon.com>
  2000-10-03 03:22 ` Re: Something I'd like to try... Bruce Momjian <pgman@candle.pha.pa.us>
  2000-10-03 04:29   ` Re: Something I'd like to try... Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-10-03 15:41     ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Josh Berkus @ 2000-10-03 15:41 UTC (permalink / raw)
  To: pgsql-sql

Tom,

> >> I just noticed that postgres doesn't totally support
> >> column aliases on UPDATE statements, for example
> 
> The SQL92 spec very clearly does not allow an alias on the target table:

I have to agree here.  The only improvement from a deviation (alllowing
aliasing) would be cosmetic. 

					-Josh
-- 
______AGLIO DATABASE SOLUTIONS___________________________
                                        Josh Berkus
   Complete information technology      josh@agliodbs.com
    and data management solutions       (415) 436-9166
   for law firms, small businesses       fax  436-0137
    and non-profit organizations.       pager 338-4078
                        		San Francisco



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Something I'd like to try...
  2000-06-09 11:18 Something I'd like to try... Niall Smart <niall.smart@ebeon.com>
  2000-10-03 03:22 ` Re: Something I'd like to try... Bruce Momjian <pgman@candle.pha.pa.us>
  2000-10-03 04:29   ` Re: Something I'd like to try... Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-10-03 17:38     ` Barry Lind <barry@xythos.com>
  2000-10-03 19:03       ` Re: Something I'd like to try... Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 7+ messages in thread

From: Barry Lind @ 2000-10-03 17:38 UTC (permalink / raw)
  To: pgsql-sql

It is interesting that this should come up now.  Just last week I was
trying to port a SQL statement from Oracle to Postgresql that used table
aliases in an update statement.  While I can see that this functionality
wouldn't be used very often it can be very useful under certain
circumstances.

I have a table that stores a hierarchy.  Sometimes in an update I want
to join back to the same table to get other information related to
children or parent rows.  In Oracle I can do this using the alias, but
in Postgresql I cannot.

Consider the SQL statements below as simplistic examples of what I was
doing in Oracle:

table_foo
  foo_id int
  parent_foo_id int
  column_a  int
  column_b  int

update table_foo f1
set column_a = (select sum(column_a) from table_foo f2
                where f2.parent_foo_id = f1.foo_id);

update table_foo f1
set column_b = 1
where exists (select column_a from table_foo f2
                  where f2.parent_foo_id = f1.foo_id);


thanks,
--Barry




Tom Lane wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Can someone comment on this?
> >>
> >> I just noticed that postgres doesn't totally support
> >> column aliases on UPDATE statements, for example
> 
> The SQL92 spec very clearly does not allow an alias on the target table:
> 
>          13.10  <update statement: searched>
> 
>          <update statement: searched> ::=
>               UPDATE <table name>
>                 SET <set clause list>
>                 [ WHERE <search condition> ]
> 
> While I'm willing to consider variations from the spec that add
> significant functionality, this proposed addition adds no functionality
> worth noticing.  It'd just be another way to trip yourself up when
> moving across DBMSes.
> 
>                         regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Something I'd like to try...
  2000-06-09 11:18 Something I'd like to try... Niall Smart <niall.smart@ebeon.com>
  2000-10-03 03:22 ` Re: Something I'd like to try... Bruce Momjian <pgman@candle.pha.pa.us>
  2000-10-03 04:29   ` Re: Something I'd like to try... Tom Lane <tgl@sss.pgh.pa.us>
  2000-10-03 17:38     ` Re: Something I'd like to try... Barry Lind <barry@xythos.com>
@ 2000-10-03 19:03       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2000-10-03 19:03 UTC (permalink / raw)
  To: Barry Lind <barry@xythos.com>; +Cc: pgsql-sql

Barry Lind <barry@xythos.com> writes:
> I have a table that stores a hierarchy.  Sometimes in an update I want
> to join back to the same table to get other information related to
> children or parent rows.  In Oracle I can do this using the alias, but
> in Postgresql I cannot.

> update table_foo f1
> set column_b = 1
> where exists (select column_a from table_foo f2
>                   where f2.parent_foo_id = f1.foo_id);

What's wrong with the spec-compliant

update table_foo
set column_b = 1
where exists (select column_a from table_foo f2
              where f2.parent_foo_id = table_foo.foo_id);

?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Something I'd like to try...
@ 2000-10-03 03:52 Cristóvão Dalla Costa <cdallacosta@bigfoot.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Cristóvão Dalla Costa @ 2000-10-03 03:52 UTC (permalink / raw)
  To: pgsql-sql

It seems to me that aliases on updates are something totally superflous.
Since updates always work on one table at a time, why bother especifying the
table each field comes from?

> Can someone comment on this?
>
> > Hi,
> >
> > I just noticed that postgres doesn't totally support
> > column aliases on UPDATE statements, for example
> >
> >          UPDATE EMPLOYEES SET
> >                  OFFICE_PHONE = UU.OFFICE_PHONE,
> >                  MOBILE_PHONE = UU.MOBILE_PHONE,
> >                  OFFICE_CD = UU.OFFICE_CD,
> >                  ABOUT_ME = UU.ABOUT_ME
> >          FROM
> >                  UNCONFIRMED_UPDATES UU
> >          WHERE
> >                  EMPLOYEES.EMPLOYEE_ID = UU.EMPLOYEE_ID AND
> >                  UU.UPDATE_ID = 'HJhjaJ023J19KJAqp'
> >
> > It is not currently possible to alias EMPLOYEES
> > so that the test can become
> >
> >          E.EMPLOYEE_ID = UU.EMPLOYEE_ID
> >
> > Do the guru's think that this would be hard to add?  Also,
> > is it desirable?
> > --






^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2000-10-03 19:03 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-09 11:18 Something I'd like to try... Niall Smart <niall.smart@ebeon.com>
2000-10-03 03:22 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-10-03 04:29   ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-03 15:41     ` Josh Berkus <josh@agliodbs.com>
2000-10-03 17:38     ` Barry Lind <barry@xythos.com>
2000-10-03 19:03       ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-03 03:52 Re: Something I'd like to try... Cristóvão Dalla Costa <cdallacosta@bigfoot.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox