At 06:28 AM 20-09-02 +0000, you wrote:

>hi,
> when i try to do this
>
>update table1 set table1.col1=
>(select table2.col2 from table2 where table2.col2=table1.col2)
>
>now it does update the rows that do match however it sets all the
>other
>rows to nul
>even if that col1 in table1 was defined as not null.
>
>this is on ib6.01 under win2k....& this is possibily the bizzarest
>thing ive
>ever seen a database do
>could anyone else confirm this please ? the above happens in ibconsole
>& also when using ibo.
>& suggest a workaround

Though it is RIGHT off-topic for the IBO list, I'll explain why this
happens...it's not bizarre, it's just SQL conforming to the logic of your
update statement. An update statement that is not restricted by a WHERE
clause, as yours is not, will operate on every row in the database.

As you observed, in the cases where your subselect returns a value, you get
the value; in all other cases you get NULL. The subselect returns NULL
for every non-match because the match predicate returns false. That's why
you are seeing what you see.

You should *never* do an update statement that is to apply to the entire
table unless you really-really-really mean it.

Perhaps this is what you intended to do:

update table1 t1 set t1.col1=
(select t2a.col2 from table2 t2a where t2a.col2=t1.col2)
where t1.col2 in (select t2b.col2 from table2 t2b)

This resort (two subselects, one correlated) will be horrendously slow and,
with your supplied example, quite unnecessary. Much simpler (if your
example reflects your real situation) would be this:

update table1
set col1 = col2
where col2 in (select col2 from table2)

If there's really more to it, it will be much quicker done by a SP
(although your supplied example is too trivial to demonstrate the point).

Helen