X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E63FFD1B17B for ; Tue, 29 Jun 2004 13:50:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87737-03 for ; Tue, 29 Jun 2004 16:50:04 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 1E702D1B174 for ; Tue, 29 Jun 2004 13:50:02 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5TGntBW001982; Tue, 29 Jun 2004 12:49:55 -0400 (EDT) To: "Alexander M. Pravking" Cc: pgsql-sql@postgresql.org Subject: Re: UPDATE ... WHERE (subselect on the same table) In-reply-to: <20040629161742.GA73173@dyatel.antar.bryansk.ru> References: <20040629161742.GA73173@dyatel.antar.bryansk.ru> Comments: In-reply-to "Alexander M. Pravking" message dated "Tue, 29 Jun 2004 20:17:42 +0400" Date: Tue, 29 Jun 2004 12:49:55 -0400 Message-ID: <1981.1088527795@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/296 X-Sequence-Number: 18241 "Alexander M. Pravking" writes: > fduch=# UPDATE test SET x = 't' > fduch-# where typ = 1 and exists ( > fduch(# SELECT 1 from test t2 > fduch(# where t2.typ = 2 and t2.name = test.name > fduch(# ); > So I have two questions: > Q1, cognitive. Why the alias for the updated table is restricted? Because the SQL standard doesn't allow an alias there. We've talked about allowing one anyway, but no one's gotten around to it. AFAICS it would only be a marginal notational advantage, not allow you to express queries you can't express today. > Q2, vital. Can I be sure that the syntax I used here will work > correctly, i.e. will the "test.name" always refer the column in outer > table, not inner (t2)? Yes. The alias *completely* hides the real name of that table reference, so "test" will never refer to "test t2". regards, tom lane