pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE ... WHERE (subselect on the same table)
3+ messages / 2 participants
[nested] [flat]
* UPDATE ... WHERE (subselect on the same table)
@ 2004-06-29 16:17 Alexander M. Pravking <fduch@antar.bryansk.ru>
2004-06-29 16:49 ` Re: UPDATE ... WHERE (subselect on the same table) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Alexander M. Pravking @ 2004-06-29 16:17 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE ... WHERE (subselect on the same table)
2004-06-29 16:17 UPDATE ... WHERE (subselect on the same table) Alexander M. Pravking <fduch@antar.bryansk.ru>
@ 2004-06-29 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-29 17:28 ` Re: UPDATE ... WHERE (subselect on the same table) Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-06-29 16:49 UTC (permalink / raw)
To: Alexander M. Pravking <fduch@antar.bryansk.ru>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: UPDATE ... WHERE (subselect on the same table)
2004-06-29 16:17 UPDATE ... WHERE (subselect on the same table) Alexander M. Pravking <fduch@antar.bryansk.ru>
2004-06-29 16:49 ` Re: UPDATE ... WHERE (subselect on the same table) Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-06-29 17:28 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 0 replies; 3+ messages in thread
From: Alexander M. Pravking @ 2004-06-29 17:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-06-29 17:28 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-29 16:17 UPDATE ... WHERE (subselect on the same table) Alexander M. Pravking <fduch@antar.bryansk.ru>
2004-06-29 16:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-06-29 17:28 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox