cannot update with null error ?????

Hi,

I am trying to update a non nullable column with certain values and am getting this error.

There are no null values but still i am getting this error


SQL> update news_storytest set PRODUCT_LOCATION_ID = (select NEW_PRODUCT_LOCATION_ID
2 from story_product_map where
3 story_product_map.STORY_ID = news_storytest.story_id);
update news_storytest set PRODUCT_LOCATION_ID = (select NEW_PRODUCT_LOCATION_ID
*
ERROR at line 1:
ORA-01407: cannot update ("COMPANY"."NEWS_STORYTEST"."PRODUCT_LOCATION_ID") to
NULL


Here is the query which proves that there are no null values in the story_product_map table
SQL> select count(*) from story_product_map where NEW_PRODUCT_LOCATION_ID is null;

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


Please suggest

because it has NOT NULL constraint?

try checking the matches between:
story_product_map.STORY_ID = news_storytest.story_id

you might find something you did not expect.

otherwise:

find out total number of rows.
then compare that to number of NEW_PRODUCT_LOCATION_ID rows that is not null

compare the two.

like:
select count(rownum)
from story_product_map;

then

select count(NEW_PRODUCT_LOCATION_ID)
from story_product_map
where NEW_PRODUCT_LOCATION_ID is NOT null;

let me know what you get.

- Magnus

I know that it has a not null constraint.

But i am not updating the column with a null value as is evident from the count(*) query in my first post.

If there are no null values in the story_product_map table in the new_product_location_id column then why am i getting this error.


Please let me know what more information u guys need to help me with this problem.

Thanks

jgmagnus has allready pointed at your problem (in the firts paragraph of his answer).

It is evident that your select

... (select NEW_PRODUCT_LOCATION_ID
2 from story_product_map where
3 story_product_map.STORY_ID = news_storytest.story_id);

is sometimes not returning any rows because there are some STORY_ID values in NEWS_STORIYTEST which do not have maching values in STORY_PRODUCT_MAP.STORY_ID). In such cases you are trying to set a NULL value for a not-null column.

Yes indeed,

There are some rows in news_storytest which are not there in story_product_map.

How do i handle such a situation.

(for now i have implemented the same functionality by using a cursor in a pl/sql block)

Thanks

you don't want to use the cursor?

oh well. anyways ...

why not make a temporary table of all matches between:
"story_product_map.STORY_ID = news_storytest.story_id"

then delete the rows that have null values

then update PRODUCT_LOCATION_ID from your temporary table.

otherwise, if you specify exactly what you want to do and how you prefer to do it, I could come up with something else.

- Magnus

To update only those rows which have corresponding STORY_ID in the STORY_PRODUCT_MAP, you only need to add a simple additional WHERE statement:

update news_storytest set PRODUCT_LOCATION_ID =
(select NEW_PRODUCT_LOCATION_ID from story_product_map
where story_product_map.STORY_ID = news_storytest.story_id)
where exists
(select NEW_PRODUCT_LOCATION_ID from story_product_map
where story_product_map.STORY_ID = news_storytest.story_id)