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