asked on

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

Sean Stuber

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

Sujith

post some sample data and the table script.

trixie111

ASKER

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

Sean Stuber

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

Sean Stuber

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

trixie111

ASKER

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.

trixie111

ASKER

sori 'rownum<=10'.

trixie111

ASKER

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

trixie111

ASKER

here's the script to insert the the values for the missions table. please see the txt file
insert-into-missions.txt

ASKER CERTIFIED SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trixie111

ASKER

thanks.

Sujith

in the above, change
>> rank() over
to
dense_rank() over

(this is to handle the repeating entries if there are any.)

trixie111

ASKER

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.....