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
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
LENGTH (SUBSTR (CODE_NAME, INSTR(CODE_NAME, ' ') +1)) > 7
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
your subquery returns null value.
check the subquery once
Click Here to Expand Forum to Full Width