updating records

[madara79]

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
)

[PAVB]

I'm not sure if I got your specifications but something tells me your code is not even close of doing what you are supposed to do.

Why you don't start by using pseudo-code so your specs would get clear and the logic could be reviewed?

[LKBrwn_DBA]

Your code seems to have too much "garbage", try the code we suggested in your other post.

[sridhar.dbe]

It seems
your subquery returns null value.
check the subquery once