From: | Timothy Perrigo <tperrigo(at)wernervas(dot)com> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: unexpected update behavior with temp tables |
Date: | 2004-07-08 14:10:50 |
Message-ID: | 9D831A92-D0E8-11D8-A4DE-000A95C4F0A2@wernervas.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Jul 8, 2004, at 8:57 AM, Richard Huxton wrote:
> Timothy Perrigo wrote:
>
>> OPT=# select id as not_id, b into temp temp_foo from foo where b =
>> 'Tim';
>> SELECT
>> OPT=# select * from temp_foo;
>> not_id | b
>> --------+-----
>> 1 | Tim
>> (1 row)
>> OPT=# update foo set b = 'Timothy' where id in (select id from
>> temp_foo);
>> UPDATE 4
>> OPT=# select * from foo;
>> id | b
>> ----+---------
>> 1 | Timothy
>> 2 | Timothy
>> 3 | Timothy
>> 4 | Timothy
>> (4 rows)
>
> I think I can see what's happening, but don't know enough internals to
> say why.
>
> The "id" in the subselect must be binding to the outer query. I could
> see how that might be desirable in some circumstances, but could
> easily cause trouble in many cases.
>
> --
> Richard Huxton
> Archonet Ltd
>
>
Richard,
I think you're probably right...I modified the temp table, renaming
both fields:
select id as not_id, b as name into temp temp_foo from foo where b =
'Tim';
Then ran the following update:
OPT=# update foo set b = 'Timothy' where b in (select b from temp_foo);
UPDATE 4
OPT=# select * from foo;
id | b
----+---------
1 | Timothy
2 | Timothy
3 | Timothy
4 | Timothy
(4 rows)
So it does look like the column in the subselect must be binding to the
outer query, though this is _not_ what I would expect to happen in this
situation.
Thanks for the response.
Tim
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2004-07-08 14:14:44 | Re: unexpected update behavior with temp tables |
Previous Message | Richard Huxton | 2004-07-08 13:57:57 | Re: unexpected update behavior with temp tables |