cannot update with null error ?????

[ronnie]

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

[pando]

because it has NOT NULL constraint?

[jgmagnus]

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

[ronnie]

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

[jmodic]

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.

[ronnie]

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

[jgmagnus]

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

[jmodic]

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)