Thread: Something I'd like to try...
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
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, Pennsylvania19026
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? > > --
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
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
informationtechnology 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
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
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