Re: Need subquery

What's the exact ORA- error message that you're getting?

If it's "ORA-01407: cannot update {column} to NULL", then it is likely that there are rows in stock1 that don't have a corresponding row in stock2. Given how you wrote your update statement, ALL rows in stock1 are supposed to be updated and when the subquery hits an unmatched row, it returns a null and thus violates the constraint on stock1.

Assuming that "unmatched" rows between stock1 and stock2 are acceptable, then how to make this update work? - You can add a WHERE EXISTS condition to the UPDATE statement. - Or if feasible, you might even write this UPDATE using an updatable inline view. See the manuals for that.

HTH. girivasan2000_at_yahoo.com (Girivasan) wrote in message news:<33361f2d.0406280515.28803d55_at_posting.google.com>...
> I need a subquery require to copy a content into another table of same
> field names. The table1 having 100 records and table2 having 50
> records
>
> Query is :
>
> update stock1 t1 set t1.amt = (select t2.amt from stock2 t2 where
> t1.pr_no = t2.pr_no)
>
> The amt column is not null constraint,
> If running this query it shows Null value error
>
> What is the reason, explain me.
Received on Mon Jun 28 2004 - 13:47:02 CDT