Re: unexpected update behavior with temp tables

On Jul 8, 2004, at 9:14 AM, Stephan Szabo wrote:

>
> On Thu, 8 Jul 2004, 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);
>
> Subselects like that are AFAIK allowed to see outer columns according 
> to
> the SQL spec.  Thus, the id inside the subselect is effectively foo.id.
>
> This behavior is useful when you want to do something like a function 
> or
> operator on an inner column and an outer column inside the subselect 
> and
> painful in cases like this where effectively the clause becomes "id is 
> not
> null" which for a primary key is itself a long way of saying "true".
>
>
Thanks for the reply, Stephan.  I guess I can see the rationale for 
this, though it is quite easy to cause yourself quite a bit of grief.  
It would certainly make things safer if columns in the subselect which 
refer to columns in the table from the outer query where required to be 
fully specified (i.e. "foo.id", instead of just "id"), but if this 
behavior is part of the standard, I imagine there's little chance of 
changing it...

I appreciate the assistance!

Tim