Обсуждение: 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