Home » SQL & PL/SQL » SQL & PL/SQL » update query
update query [message #353406] Mon, 13 October 2008 14:23 Go to next message
vchimakurthi@gmail.com
Messages: 3
Registered: October 2008
Junior Member
hai

i got table missions

mission_id,
mission_type_id
security_level
code_name etc

i need to write query

update the table for those missions
-within the 10 most recent missions
-length of the first word of the mission code_name exceeds 7 characters

change the security level to the highest security level found in missions of the same type.

but i stuck near update it returns missions with 2 conditions satisfied but need to set security_level to highst level


UPDATE AM_X_442_2
SET    SECURITY_LEVEL = *****?
WHERE  (MISSION_ID,
        MISSION_TYPE_ID) IN (SELECT MISSION_ID,
                                    MISSION_TYPE_ID
                             FROM   AM_X_442_2
                             WHERE  MISSION_ID IN (SELECT MISSION_ID
                                                   FROM   (SELECT   MISSION_ID
                                                           FROM     AM_X_442_2
                                                           ORDER BY MISSION_DATE DESC)
                                                   WHERE  ROWNUM <= 10
                                                          AND LENGTH(CODE_NAME) > 7))

[Updated on: Mon, 13 October 2008 22:02] by Moderator

Report message to a moderator

Re: update query [message #353409 is a reply to message #353406] Mon, 13 October 2008 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Sat, 11 October 2008 18:18
From your previous post:
Michel Cadot wrote on Sat, 11 October 2008 07:58
Post a test case: create table and insert statements along with the result you want with these data.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel



Re: update query [message #353435 is a reply to message #353406] Mon, 13 October 2008 22:19 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Use the Tools in the reply Page to format the codes .
Otherwise don't expect us to reply to you from next time.

Just update it to the MAX(security_level) using the same type of clauses.

Can't you simplify the code section

(SELECT MISSION_ID,
        MISSION_TYPE_ID
 FROM   AM_X_442_2
 WHERE  MISSION_ID IN (SELECT MISSION_ID
                       FROM   (SELECT   MISSION_ID
                               FROM     AM_X_442_2
                               ORDER BY MISSION_DATE DESC)
                      WHERE  ROWNUM <= 10
                      AND LENGTH(CODE_NAME) > 7))


to

SELECT MISSION_ID,
       MISSION_TYPE_ID
FROM   (SELECT   MISSION_ID,
                 MISSION_TYPE_ID
        FROM     AM_X_442_2
        ORDER BY MISSION_DATE DESC)
WHERE  ROWNUM <= 10
AND LENGTH(CODE_NAME) > 7


Btw, why did you use

Quote:
WHERE (MISSION_ID,
MISSION_TYPE_ID


and

Quote:
WHERE MISSION_ID IN
in various instances ?

Thumbs Up
Rajuvan.

Previous Topic: ORA-01031
Next Topic: about bfile (merged 4)
Goto Forum:
  


Current Time: Wed Dec 07 05:13:58 CST 2016

Total time taken to generate the page: 0.23802 seconds