I am updating the table t, to update the prices , so a null will not be acceptable to me, so let me rewrite the update accoridng to your advise.

Update t

set t.col1=(select t.co1+t1.col1
where t.key=t1.key
and t.1.id=(select id from t3
where t3.dt= somedate and
t3.col6=rec.col6..........)),
t.col2=select t.co1+t1.col1
where t.key=t1.key
and t.1.id=(select id from t3
where t3.dt= somedate....
t3.col6=rec.col6..........))
where t.col3 =something and
t.col4=something and
exists( select id from t3
where t3.dt= somedate and
t3.col6=rec.col6..........)

I added the exists to the end with the innermost subquery as you have suggested in your example.

1.But please not that , Iam updating 2 columns, and the inner most subquery differs for both of them to the effec taht the t3.dt= somedate will differ. Now which one of these inner most subqueries should go into the exists clause.

2.Actually, how is the exists clause helping us in avoiding a null

(It could have been ideal had you given an example updating 2 columns, and updating the columns by adding to the updating columns in the update.)

1.Actually, how is the exists clause helping us in avoiding a null.

2.Is this kind of an update given else where on your websidte , and in oracle documentation

my objective is to update the column x in table t to zero, if the inner most query returns no data, but Iam finding that, even if I use nvl , it is updating to null. Is this the expected  behaviour or Iam I missing something.


SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                                NUMBER(10)

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                         NOT NULL NUMBER(38)
 Y                                                  NUMBER(38)

SQL> select count(*) from t1;

  COUNT(*)
----------
         0





SQL>  select * from t;

       X
----------
     1



SQL> update t set X = ( select nvl(y,0) from t2 where y in ( select nvl(y,0)
  2  from t2 ) );

1 row updated.

SQL> commit;

Commit complete.


QL> select * from t
 2  /

      X
--------- 

You are missing the subtle point that the subquery returns NO rows -- there is "nothing to nvl" as it produces no output.  Hence, what you really really what is the NVL of the subquery!

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x int );
Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t2 ( y int );
Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values ( 1 );
1 row created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> update t 
                                   <b>set x = nvl( ( select y from t2 ), 0 );</b>
1 row updated.

ops$tkyte@ORA717DEV.US.ORACLE.COM> select * from t;

         X
----------
         0


nvl( subquery, 0 ), not subquery with NVL. 

Hi Tom;

I have the same problem, when I need to update a table and
set columns to some value, if the subquery does not return
any data.

You suggested:

update t
set x = nvl( ( select y from t2 ), my_value );

and it works fine in SQLPlus, but not in the stored procedure.

I am running this on Oracle 8.1.7. Is it possible in 8.1.7 or do I have to wait till we upgrade to 9.x?

Thanks in advance

Hi Tom,
I am geeting no_data_found in folllowing update statement (it's in stored procedure)

IF s_last_rep.count != 0 THEN
FOR jj IN s_last_rep.FIRST .. s_last_rep.LAST LOOP

dbms_output.put_line( s_last_rep(jj).loss || ' C' ||s_last_rep(jj).cancel||'P '||s_last_rep(jj).pastdue|| '' ||s_last_rep(jj).ed_year );


BEGIN
UPDATE check_edition
SET last_loss = NVL(s_last_rep(jj).loss,last_loss),
last_cancel = NVL(s_last_rep(jj).cancel,last_cancel),
last_pastdue = NVL(s_last_rep(jj).pastdue,last_pastdue)
WHERE edition_year = s_last_rep(jj).ed_year;
EXCEPTION
WHEN others then
dbms_output.put_line('ERROR ' ||SQLERRM);
end;
END LOOP;

END IF;

Could you please help?

it must have a trigger on it, updates in themselves do not get "no data found", only a select into would. (or the reference of a subscript in a plsql index by table that doesn't exist...)

 
ops$tkyte@ORA9IR2> update t set x = x where 1=0;
 
0 rows updated.
 


so, look for triggers and make sure jj is in range (eg: first..last isn't necessarily correct - it could have 1, 3, 5, and 7 but not 2, 4, 6...) 

You are right Tom.

subscript in a plsql index by table didn't exist.

Thanks for the help

I did what you mentioned in the clause and use exists clause.
But when i look at the explain plan and find its cost.Earlier it was showing 157 but its showing 1570 by using exists clause .
Please suggest.