Re: Simple update statement (or is it?)

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 11 Feb 2010 21:49:26 +0100
Message-ID: <4B746D56.8070308_at_gmail.com>

On 11.02.2010 20:27, Sashi wrote:
> I have a very simple update statement. My version is
> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit
> Production
>
> Update A
> set A.col3 = (select col3 from B where B.col1 = A.col1 and B.col2 =
> A.col2)
> where a.col3 is null;
>
> When the statement runs, it confirms that 291 rows have been updated.
>
> However, when I run select count(*) from A where col3 is null, I get
> the result as 648 before AND after running the above update. And, yes,
> I'm issuing a commit after the update statement.
>
> I'm left scratching my head.
>
> Is there something wrong with my update statement? I looked up some
> examples and psoug.org has some that match my syntax above.
>
> Thanks for any pointers!
> Sahsi

If a row get updated, it doesn't necessarily means, it will be updated with a *not null* value. Look at the example:

SQL> create table a as

   2  select 1 col1,1 col2,cast(null as number) col3 from dual union all
   3  select 2 col1,2 col2,cast(null as number) col3 from dual union all
   4  select 3 col1,3 col2,cast(null as number) col3 from dual

   5 ;

Table created.

SQL> create table b as

   2 select 1 col1,1 col2,1 col3 from dual union all    3 select 3 col1,3 col2,cast(null as number) col3 from dual    4 ;

Table created.

SQL> select * from a;

       COL1 COL2 COL3
---------- ---------- ----------

          1          1
          2          2
          3          3

3 rows selected.

SQL> select * from b;

       COL1 COL2 COL3
---------- ---------- ----------

          1          1          1
          3          3

2 rows selected.

SQL> update a

   2 set a.col3 = (select col3 from b where b.col1 = a.col1 and b.col2 =    3 a.col2)
   4 where a.col3 is null;

3 rows updated.

SQL> select * from a;

       COL1 COL2 COL3
---------- ---------- ----------

          1          1          1
          2          2
          3          3

3 rows selected.

Here, the second rows ( with col1=2) was updated, but subquery got any matched results, so it will be updated to NULL for col3. The third row (col1=3) will also be updated, where will be a matching row from the subquery, but the value returned for col3 is NULL as well, so NULL in both cases will be updated with NULL.

Best regards

Maxim Received on Thu Feb 11 2010 - 14:49:26 CST