cannot update with null error ?????
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: cannot update with null error ?????

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    because it has NOT NULL constraint?

  3. #3
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    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

  4. #4
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    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.

  7. #7
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking 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

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width