From: | "Alexander M(dot) Pravking" <fduch(at)antar(dot)bryansk(dot)ru> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | UPDATE ... WHERE (subselect on the same table) |
Date: | 2004-06-29 16:17:42 |
Message-ID: | 20040629161742.GA73173@dyatel.antar.bryansk.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-06-29 16:18:17 | Re: Unrecognized node type |
Previous Message | Tom Lane | 2004-06-29 14:57:45 | Re: FW: "=" operator vs. "IS" |