Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by hub.org (8.10.1/8.10.1) with ESMTP id e93J41G05357 for ; Tue, 3 Oct 2000 15:04:01 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.0/8.11.0) with ESMTP id e93J3gB29639; Tue, 3 Oct 2000 15:03:42 -0400 (EDT) To: Barry Lind cc: pgsql-sql@postgresql.org Subject: Re: Something I'd like to try... In-reply-to: <39DA19AA.9C87067A@xythos.com> References: <200010030322.XAA05358@candle.pha.pa.us> <16699.970547357@sss.pgh.pa.us> <39DA19AA.9C87067A@xythos.com> Comments: In-reply-to Barry Lind message dated "Tue, 03 Oct 2000 10:38:50 -0700" Date: Tue, 03 Oct 2000 15:03:41 -0400 Message-ID: <29636.970599821@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200010/39 Barry Lind 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