Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    6

    Unanswered: Update specific records

    hi, i am tryin to update 6 specific records out of 400 odd. Problem is my statement is updating all 400 records.

    UPDATE AM_X_422_2
    SET
    security_level =
    (
    SELECT
    MAX(security_level)
    FROM
    (
    SELECT
    code_name,
    LENGTH(code_name),
    security_level,
    mission_date
    FROM
    AM_X_422_2
    ORDER BY
    mission_date desc
    )
    WHERE
    rownum < 11
    )
    WHERE
    LENGTH(code_name) > 7
    ;


    I want to update the security level to the highest security level for missions which are within the 10 most recent missions and where length of the first word of the mission code_name exceeds 7 characters.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It appears that all records (400 of them?) in the AM_X_422_2 table have "code_name" column's value longer than 7 characters. Is it really so? What is "code_name" column''s datatype? (I hope it is not CHAR!) What is the result of
    Code:
    select length(code_name), count(*)
    from am_x_422_2
    group by length(code_name)

  3. #3
    Join Date
    Oct 2008
    Posts
    6
    LENGTH(CODE_NAME) COUNT(*)
    6 64
    11 85
    13 24
    14 14
    2 1
    5 24
    4 18
    8 107
    17 1
    7 99
    3 3
    10 92
    9 86
    12 42
    15 8

    data type of code_name is varchar2. This is the code for selecting the 6 rows now i just want to update it.

    SELECT * FROM
    (SELECT *
    FROM
    (SELECT
    code_name, LENGTH(code_name), security_level, mission_date
    FROM
    AM_X_422_2
    ORDER BY
    mission_date
    desc)
    WHERE
    rownum < 11)
    WHERE
    LENGTH(code_name) > 7;

  4. #4
    Join Date
    Oct 2008
    Posts
    6
    statement has been changed to this:

    UPDATE AM_X_422_2 am
    SET
    am.security_level =
    (
    SELECT
    lev
    FROM
    (
    SELECT
    max(am.security_level) lev
    FROM
    (
    SELECT
    name,
    no,
    am.security_level
    FROM
    (
    SELECT
    am.code_name name,
    LENGTH(am.code_name) no,
    am.security_level,
    am.mission_date
    FROM
    AM_X_422_2 am
    ORDER BY
    am.mission_date desc
    )
    WHERE
    rownum < 11
    )
    WHERE
    LENGTH(name) > 7
    )over
    WHERE
    over.lev= am.security_level)
    Last edited by jay_pink_elephant; 10-20-08 at 13:56.

  5. #5
    Join Date
    Oct 2008
    Posts
    6
    forgot to mention the code brings up the ORA-01407 error, cannot update to null value. I did check the select statement which does return a max value

Posting Permissions

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