Received: from ns.sharemation.com (d83b57e4.dsl.flashcom.net [216.59.87.228]) by hub.org (8.10.1/8.10.1) with ESMTP id e93HTV779380 for ; Tue, 3 Oct 2000 13:29:31 -0400 (EDT) Received: from xythos.com (IDENT:blind@frog.xythos.com [192.168.254.110]) by ns.sharemation.com (8.9.3/8.8.7) with ESMTP id KAA28244 for ; Tue, 3 Oct 2000 10:20:47 -0700 Message-ID: <39DA19AA.9C87067A@xythos.com> Date: Tue, 03 Oct 2000 10:38:50 -0700 From: Barry Lind X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.14-5.0smp i686) X-Accept-Language: en,pdf MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Something I'd like to try... References: <200010030322.XAA05358@candle.pha.pa.us> <16699.970547357@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Archive-Number: 200010/38 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 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 > SET > [ WHERE ] > > 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