I want to update the security level to the highest security level found in missions of the same type. These should be within the 10 most recent missions where the length of the first word of the mission code_name should exceed 7. I managed to do it with just plain SELECT query, but when I tried to implement it with an UPDATE, it doesn't work. Please help.
UPDATE missions m
FROM missions m3
(CASE WHEN INSTR(m.code_name,' ')=0
ELSE INSTR(m.code_name, ' ')-1