UPDATE ... WHERE (subselect on the same table)
От | Alexander M. Pravking |
---|---|
Тема | UPDATE ... WHERE (subselect on the same table) |
Дата | |
Msg-id | 20040629161742.GA73173@dyatel.antar.bryansk.ru обсуждение исходный текст |
Ответы |
Re: UPDATE ... WHERE (subselect on the same table)
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: