Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2004-06-29 16:18:17
Subject: Re: Unrecognized node type
Previous:From: Tom LaneDate: 2004-06-29 14:57:45
Subject: Re: FW: "=" operator vs. "IS"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group