Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2014
    Posts
    1

    Unanswered: update on the table by extracting the data from the same table based on few condition

    Hi Experts,

    I have EMP table which currently has 6 records.

    EMP_SYS BATCH_NO JOIN_DATE FLAG LOGIN_ID STATUS
    PROD 1 10-FEB-84 Y IND123 ABC
    DEV 1 23-APR-98 Y IND123 ABC
    PROD 2 12-APR-98 Y IND123 ABC
    DEV 2 2-APR-98 Y IND123 ABC
    PROD 3 13-APR-98 Y IND123 ABC
    DEV 3 3-APR-98 Y IND123 ABC

    CONDITION : I need to take the rows which has the minimum BATCH_NO for each of the EMP_SYS

    i.e.,

    PROD 1 10-FEB-84 Y IND123 ABC
    DEV 1 23-APR-98 Y IND123 ABC

    For the above two rows i need to update the status to DEF . How to select with this criteria and update in the same query ?

    Currently i have used the below query to select the minimum BATCH_NO for each of the EMP_SYS .

    SELECT EMP_SYS, BATCH_NO, JOIN_DATE, FLAG, LOGIN_ID, STATUS
    FROM
    (
    SELECT EMP_SYS, BATCH_NO, JOIN_DATE, FLAG, LOGIN_ID, STATUS, ROW_NUMBER ()
    OVER (PARTITION BY EMP_SYS ORDER BY EMP_SYS) AS NR
    FROM EMP
    WHERE STATUS = 'ABC' AND FLAG = 'Y'
    )
    WHERE
    NR = 1;

    If i try to use the update statement on the above query as specified below...

    update EMP e
    set STATUS='DEF'
    where BATCH_NO = (select min(BATCH_NO) from EMP i where STATUS = 'ABC' AND FLAG = 'Y' and i.emp_sys=e.emp_sys)..


    The query is working for the scenario where both the "emp_sys"s BATCH_NO starts with 1.
    i.e

    PROD 1 10-FEB-84 Y IND123 ABC
    DEV 1 23-APR-98 Y IND123 ABC


    But i have the scenario like below.

    PROD 2 10-FEB-84 Y IND123 ABC
    DEV 1 23-APR-98 Y IND123 ABC

    The query is updating only the row which has the BATCH_NO=1 in the first run and it will update the other row(BATCH_NO=2) in the second run.

    But this should not be the case.Both the rows should update in the first run itself since the BATCH_NO=2 is the minimum value for the emp_sys=PROD and BATCH_NO=1 is the minimum value for emp_sys=DEV.

    Thank you.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >(select min(BATCH_NO) from EMP i where STATUS = 'ABC' AND FLAG = 'Y' and i.emp_sys=e.emp_sys)

    above will only ever return a single value due to MIN(BATCH_NO)
    Besides if it were to return more than 1 row; it would throw error since BATCH_NO can not contain more than 1 value.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

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