On 2006-02-05, Niederland <niederland@gmail.com> wrote:
> I am using postgres 8.1.2 on a windows computer.
> My postgres.config has:
> add_missing_from = off
>
> In the following example, the first update sets value for v1 in table
> t1 to 4 for all rows.
> Just for my sanity, I checked the null condition in the second update,
> and this
> does not update any of the rows, am I missing something?
>
> create table t1 (v1 integer);
> create table t2 (v2 integer);
>
> insert into t1 (v1) values (1);
> insert into t1 (v1) values (2);
>
> insert into t2 (v2) values (3);
>
> update t1 set v1=4 where v1 IN (select v1 from t2);
What's happening here is that since t2 has no column "v1", the reference
to v1 in the subquery is bound to v1 in the nearest outer query level,
i.e. the update itself. So the subquery becomes correlated, and returns
true for all non-null values of v1 as long as t2 has at least one row.
> update t1 set v1=4 where v1 IN (select NULLIF(1,1) from t2);
v1 IN (NULL) is null for all v1, and WHERE treats null conditions as false.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
