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

On 11.02.2010 20:27, Sashi wrote:
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

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