Results 1 to 4 of 4

Thread: updating data

  1. #1
    Join Date
    Jan 2009
    Posts
    11

    Unanswered: updating data

    I am trying to update the security_level of a mission to the highest security level of mimssions of the same type.

    Attributes of the missions table:
    mission_id, code_name, mission_type_id, mission_date, security_level

    The following is an intermediate output.
    MISSION_ID MISSION_TYPE_ID SECURITY_LEVEL
    318 3 6
    329 3 2
    286 5 6
    521 5 3
    281 6 4
    396 7 3
    331 8 4
    14 9 4
    230 9 0
    486 10 2


    The maximum output for each mission_type_id

    MAX_LEVEL TYPE
    6 3
    6 5
    4 6
    3 7
    4 8
    4 9
    2 10

    According to this 3 records (329,521 and 230) should update.

    But my code returns an error.
    ERROR at line 4:
    cannot update (......"SECURITY_LEVEL") to NULL

    can someone help meto find the fault?

    this is my code

    UPDATE
    AM_X_442_2 amx
    SET
    Amx.SECURITY_LEVEL =
    (
    select
    max_level
    from (
    select
    max(security_level) max_level,
    mission_type_id type
    from (
    SELECT
    mission_id,
    MISSION_TYPE_ID,
    security_level
    FROM
    (
    SELECT
    MISSION_ID,
    MISSION_TYPE_ID,
    SECURITY_LEVEL
    FROM
    AM_X_442_2 m
    WHERE
    LENGTH (SUBSTR (CODE_NAME, INSTR(CODE_NAME, ' ') +1)) > 7
    ORDER BY
    MISSION_DATE DESC
    )
    WHERE
    ROWNUM <=10
    GROUP BY
    mission_id,
    MISSION_TYPE_ID,
    security_level
    ORDER BY
    MISSION_TYPE_ID
    )
    GROUP BY
    MISSION_TYPE_ID
    ) ten_missions
    WHERE ten_missions.type = amx.MISSION_TYPE_ID
    )

    I have indended the query to make it legible but it removes all spaces. I'm sorry it's quite difficult to read.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://www.dbforums.com/oracle/10316...s-posters.html

    Concentrate on <codetags>

    Post DDL for tables.
    Post DML for needed test/sample data
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by madara79
    According to this 3 records (329,521 and 230) should update.
    As your UPDATE statement is unreadable without formatting (it is both without code tags and indenting), the only thing I am willing to watch is the brackets and table aliases. They lead to the conclusion, that the above statement is not true - as UPDATE statement has no WHERE condition, all rows are updated.

    Have a look at this thread to get an idea what is happening: http://www.dbforums.com/oracle/16299...mn-update.html.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down Another spaguetti code.

    You code is full of unnecessary c.r.a.p., just try the code we suggested on your other post.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •