Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Exclamation Unanswered: Updating the top row

    Hi,

    I want to update the top row resulted from a filiter condition and my query is

    UPDATE il50.WORKORDERTABLE W SET W.ENDFLAG = 1 WHERE EXISTS (SELECT WM.WORKORDERID FROM il50.WORKORDERMACHINETABLE WM,il50.MACHINETABLE M,il50.NODETABLE N WHERE WM.MACHINEID = M.MACHINEID AND M.NODEID = N.NODEID AND W.ENDDATE IS NULL AND W.STARTDATE > TO_DATE('2004-01-01 00:00:00', 'YYYY-MM-DD HH24:MIS'))
    AND ROWNUM = 1 ORDER BY STARTDATE ASC

    But I am always getting the same row even if I change the order from ASC to DESC. But when I checked the select statement without ROWNUM condition, it is returning 2 rows and also the rows are displayed in the order that I mentioned in the query.

    Is there anything I am missing out here?

    Thanks in advance
    Sateesh.

  2. #2
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re:

    Hi,

    I am able to achieve it using an outer and an inner query and the query is

    SELECT * FROM
    (SELECT * FROM il50.WORKORDERTABLE W WHERE EXISTS (SELECT WM.WORKORDERID FROM il50.WORKORDERMACHINETABLE WM,il50.MACHINETABLE M,il50.NODETABLE N WHERE WM.MACHINEID = M.MACHINEID AND M.NODEID = N.NODEID AND W.ENDDATE IS NULL AND W.STARTDATE > TO_DATE('2004-01-01 00:00:00', 'YYYY-MM-DD HH24:MIS'))
    ORDER BY W.STARTDATE ASC)
    WHERE ROWNUM = 1

    But I want to update this row. I am not sure how I can do it. Does anybody tell me.

    Thanks
    Sateesh.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Don't use "WHERE ROWNUM = 1"
    Use MIN or MAX depending upon ASCENDING or DESCENDING

  4. #4
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re:

    Guru,

    I want to update the row that has maximum value for workorderid in the above query.

    Do you pls. tell me how to write it.

    Thanks
    Sateesh.

    Originally posted by anacedent
    Don't use "WHERE ROWNUM = 1"
    Use MIN or MAX depending upon ASCENDING or DESCENDING

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •