pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSomething 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>
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-10-03 03:22 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Niall Smart <niall.smart@ebeon.com>
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-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
* Re: Something I'd like to try...
@ 2000-10-03 04:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
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-10-03 15:41 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
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-10-03 17:38 Barry Lind <barry@xythos.com>
parent: 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-10-03 19:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Barry Lind <barry@xythos.com>
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
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