-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
because it has NOT NULL constraint?
-
other methods
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
use the cursor baby!
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)
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|