Re: unexpected update behavior with temp tables

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