Re: UPDATE ... WHERE (subselect on the same table)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: UPDATE ... WHERE (subselect on the same table)
Date: 2004-06-29 16:49:55
Message-ID: 1981.1088527795@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

"Alexander M. Pravking" <fduch(at)antar(dot)bryansk(dot)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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Alexander M. Pravking 2004-06-29 17:28:49 Re: UPDATE ... WHERE (subselect on the same table)
Previous Message beyaNet 2004-06-29 16:31:43 test1