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

"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