Re: Simple update statement (or is it?)

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 11 Feb 2010 21:56:07 +0100
Message-ID: <4b746ef5$0$6582$9b4e6d93_at_newsspool3.arcor-online.net>

On 11.02.2010 21:49, Maxim Demenko wrote:
> 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

The thing however with different counts for the same where clause is a little bit scary - maybe there are concurrent transactions in parallel sessions?
Could you post the output from the sqlplus

set feedback on
select count(*) from a where col3 is null; update a set col3=null where col3 is null;

?

Best regards

Maxim Received on Thu Feb 11 2010 - 14:56:07 CST