How to I make a third outer loop in an UPDATE correlated subquery?

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
SET
    m.security_level=
    (
      SELECT
             MAX(m2.security_level)
      FROM
           (
            SELECT
                   *
             FROM missions m3
             ORDER BY
                   m3.mission_date DESC
            )
       WHERE
            rownum<=10
      AND
            m3.mission_type_id=m3.mission_type_id
     )
WHERE
      (CASE WHEN INSTR(m.code_name,' ')=0
                 THEN LENGTH(m.code_name)
       ELSE INSTR(m.code_name, '  ')-1
       END)>7 ;

Open in new window

trixie111Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
What is it you're trying to do?


 m3.mission_type_id=m3.mission_type_id  

this is the problem for 2 reasons.  First, the syntax error,  you are referencing m3, but m3 is an alias inside the inner query,  so it doesn't apply in the outer scope
second,  what is the purpose of that clause?  Every row will be equal to itself unless it's null
0
SujithData ArchitectCommented:
post some sample data and the table script.
0
trixie111Author Commented:
I'm trying to update the security level according to this subquery:
SELECT 
       mission_id,
       code_name,
    CASE WHEN INSTR(code_name,' ')=0
                 THEN LENGTH(code_name)
       ELSE INSTR(code_name, '  ')-1
       END AS first_word_chars,
       location_id,
       mission_type_id,
       mission_date,
       security_level
FROM 
  (
    SELECT *
    FROM
      (
       SELECT*
       FROM missions m1
       ORDER BY
       mission_date DESC
      )
    WHERE
      rownum<=10
    )
WHERE
  (CASE WHEN INSTR(code_name,' ')=0
                 THEN LENGTH(code_name)
       ELSE INSTR(code_name, '  ')-1
       END)>7;
 

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
try this...
UPDATE   missions m
   SET   m.security_level   =
             (SELECT   MAX(x.security_level)
                FROM   (  SELECT   security_level, mission_type_id
                            FROM   missions
                        ORDER BY   mission_date DESC) x
               WHERE   ROWNUM <= 10 AND x.mission_type_id = m.mission_type_id)
 WHERE   (CASE
              WHEN INSTR(m.code_name, ' ') = 0 THEN LENGTH(m.code_name)
              ELSE INSTR(m.code_name, '  ') - 1
          END) > 7;

Open in new window

0
sdstuberCommented:
ooops, no.  Try this....
UPDATE   missions m
   SET   m.security_level   =
             (SELECT   MAX(x.security_level)
                FROM   (SELECT   security_level,
                                 mission_type_id,
                                 ROW_NUMBER()
                                     OVER (PARTITION BY mission_type_id ORDER BY mission_date DESC)
                                     rn
                          FROM   missions) x
               WHERE   rn <= 10 AND x.mission_type_id = m.mission_type_id)
 WHERE   (CASE
              WHEN INSTR(m.code_name, ' ') = 0 THEN LENGTH(m.code_name)
              ELSE INSTR(m.code_name, '  ') - 1
          END) > 7;

Open in new window

0
trixie111Author Commented:
it updated 450 rows, it should only update 6 rows, within the 10 rows with the most recent missions. the inner loop should include the where clause 'rownum<=7'. then the outer loop should have the where clause 'case when instr...'statement.

first it selects the 10 most recent date, then it selects the rows where the first word of the code name >7.
0
trixie111Author Commented:
sori 'rownum<=10'.
0
trixie111Author Commented:
here's the table script:


CREATE TABLE "MISSIONS"    (	"MISSION_ID" NUMBER(8,0) NOT NULL ENABLE, 	"CODE_NAME" VARCHAR2(50) NOT NULL ENABLE, 	"LOCATION_ID" NUMBER(8,0) NOT NULL ENABLE, 	"MISSION_TYPE_ID" NUMBER(8,0) NOT NULL ENABLE, 	"MISSION_DATE" DATE, 	"SECURITY_LEVEL" NUMBER(8,0) NOT NULL ENABLE, 	 CONSTRAINT "MISSIONS_PK" PRIMARY KEY ("MISSION_ID")  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "USERS"  ENABLE, 	 CONSTRAINT "MISSIONS_LOCATIONS_FK1" FOREIGN KEY ("LOCATION_ID")	  REFERENCES "LOCATIONS" ("LOCATION_ID") ENABLE, 	 CONSTRAINT "MISSIONS_MISSION_TYPES_FK1" FOREIGN KEY ("MISSION_TYPE_ID")	  REFERENCES "MISSION_TYPES" ("MISSION_TYPE_ID") ENABLE   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  TABLESPACE "USERS"  ;

Open in new window

0
trixie111Author Commented:
here's the script to insert the the values for the missions table. please see the txt file
insert-into-missions.txt
0
SujithData ArchitectCommented:
This should do.
update missions M
set m.security_level = ( select security_level
                         from 
			 (
				select mission_type_id, code_name, security_level, mission_date
				from (
				select mission_type_id, code_name, security_level, mission_date, rank() over(order by mission_date desc) rn
				from missions )
				where rn <= 10 and (CASE WHEN INSTR(code_name,' ')=0 THEN LENGTH(code_name) ELSE INSTR(code_name, '  ')-1 END)>7 
                         ) Z
			 where Z.mission_type_id = M.mission_type_id and Z.code_name = M.code_name
		       )
Where EXISTS ( select 1
                         from 
			 (
				select mission_type_id, code_name, security_level, mission_date
				from (
				select mission_type_id, code_name, security_level, mission_date, rank() over(order by mission_date desc) rn
				from missions )
				where rn <= 10 and (CASE WHEN INSTR(code_name,' ')=0 THEN LENGTH(code_name) ELSE INSTR(code_name, '  ')-1 END)>7 
                         ) Z
			 where Z.mission_type_id = M.mission_type_id and Z.code_name = M.code_name
             );

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trixie111Author Commented:
thanks.
0
SujithData ArchitectCommented:
in the above, change
>> rank() over
to
dense_rank() over

(this is to handle the repeating entries if there are any.)
0
trixie111Author Commented:
the code is working alright, but one value in 6 rows that were updated should have a security level of 6, it has a security level of 5 bec of the rn<=10 and case statement. rn<10 shoulb be separate.

rn<=10 should be in the inner loop and case...instr should be in the outer loop
e.g.
   select.....
   from....
      (  
      select ...
      from...
      where
         rn<=10
   where
      case instr.....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.