-
updating records
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'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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Spaguetti code anyone?
Your code seems to have too much "garbage", try the code we suggested in your other post.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
It seems
your subquery returns null value.
check the subquery once
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|