[jay_pink_elephant]

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.

[Littlefoot]

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

select length(code_name), count(*)
from am_x_422_2
group by length(code_name)

[jay_pink_elephant]

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;

[jay_pink_elephant]

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)

[jay_pink_elephant]

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