From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alexander M. Pravking <fduch@antar.bryansk.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: UPDATE ... WHERE (subselect on the same table)
Date: Tue, 29 Jun 2004 12:49:55 -0400
Message-ID: <1981.1088527795@sss.pgh.pa.us> (raw)
In-Reply-To: <20040629161742.GA73173@dyatel.antar.bryansk.ru>
References: <20040629161742.GA73173@dyatel.antar.bryansk.ru>
"Alexander M. Pravking" <fduch@antar.bryansk.ru> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, fduch@antar.bryansk.ru
Subject: Re: UPDATE ... WHERE (subselect on the same table)
In-Reply-To: <1981.1088527795@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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