madara79

My solution to the following question update 10 rows. It should update only 6 records.

Create a copy of the missions table called AM_X_442_2. For missions on this table that meet the following criteria:
1. within the 10 most recent missions
2. length of the first word of the mission code_name exceeds 7 characters

Do the following:
Change the security level to the highest security level found in missions of the same type.


UPDATE
AM_X_442_2 am
SET
am.SECURITY_LEVEL =
(
SELECT
MAX(amx.SECURITY_LEVEL)
FROM
AM_X_442_2 am
INNER JOIN
AM_X_442_2 amx
ON
am.MISSION_TYPE_ID = amx.MISSION_TYPE_ID
WHERE
am.MISSION_ID in
(
SELECT
MISSION_ID
FROM
(
SELECT
MISSION_ID
FROM
AM_X_442_2
WHERE
LENGTH (SUBSTR (CODE_NAME, INSTR(CODE_NAME, ' ') +1)) > 7
ORDER BY
MISSION_DATE DESC
)
WHERE
ROWNUM <11
)
)
WHERE am.MISSION_ID IN
(
SELECT
MISSION_ID
FROM
(
SELECT
MISSION_ID
FROM
AM_X_442_2
WHERE
LENGTH (SUBSTR (CODE_NAME, INSTR(CODE_NAME, ' ') +1)) > 7
ORDER BY
MISSION_DATE DESC
)
WHERE
ROWNUM <11
)