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