?
Solved

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

Posted on 2008-11-19
13
Medium Priority
?
636 Views
Last Modified: 2013-12-07
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

0
Comment
Question by:trixie111
  • 7
  • 3
  • 3
13 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 23000604
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
 
LVL 27

Expert Comment

by:sujith80
ID: 23000619
post some sample data and the table script.
0
 

Author Comment

by:trixie111
ID: 23000626
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 23000636
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 23000640
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
 

Author Comment

by:trixie111
ID: 23000690
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
 

Author Comment

by:trixie111
ID: 23000699
sori 'rownum<=10'.
0
 

Author Comment

by:trixie111
ID: 23000718
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
 

Author Comment

by:trixie111
ID: 23000740
here's the script to insert the the values for the missions table. please see the txt file
insert-into-missions.txt
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 2000 total points
ID: 23000893
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
 

Author Comment

by:trixie111
ID: 23001043
thanks.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 23001078
in the above, change
>> rank() over
to
dense_rank() over

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

Author Comment

by:trixie111
ID: 23004687
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question