Simple update statement (or is it?)
in [Oracle Help]
|
From: Sashi on 11 Feb 2010 14:27 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
From: Maxim Demenko on 11 Feb 2010 15:49 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
From: Maxim Demenko on 11 Feb 2010 15:56 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
|
Pages: 1 Prev: Creating Tablespace in sql developer (Oracle 11g) Next: sql select to file using java |