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

I was looking for how can I give an alias for the table being updated
(something like UPDATE table_name table_alias SET ...), but the current
syntax doesn't allow that.

What I need is to:

fduch=# SELECT * from test order by typ, name;typ | name | x
-----+------+---  1 | bar  |  1 | baz  |  1 | foo  |  2 | baz  |  2 | foo  |
(5 rows)

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(# );
UPDATE 2
fduch=# SELECT * from test order by typ, name;typ | name | x
-----+------+---  1 | bar  |  1 | baz  | t  1 | foo  | t  2 | baz  |  2 | foo  |
(5 rows)

So I have two questions:
Q1, cognitive. Why the alias for the updated table is restricted?
Is there any reason for that or it's just not implemented?

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)?

Thanks in advance.

-- 
Fduch M. Pravking

On Tue, Jun 29, 2004 at 12:49:55PM -0400, Tom Lane wrote:
> > 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".

As always, perfectly clear, thank you Tom :)

I already found in docs on SELECT:
When an alias is provided, it completely hides the actual name of the
table or table function;

/me should RTFM... (repeating hundred times)

-- 
Fduch M. Pravking

"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