Link to home
Start Free TrialLog in
Avatar of dmissel
dmisselFlag for United States of America

asked on

Need to turn a Select SQL into an update

I have the code below that selects a record based on a nested statement. I need to update the output record ps_job.elig_config4 with the value  returned in the sub-select ps_job.elig_config4


SELECT tr_job_chg_id,
                 emplid,
                 action_dt_ss,
                 EFFSEQ,
                 TR_OFF_TITL_CHG
    FROM ps_tr_job_chg_tbl A
        WHERE A.TR_OFF_TITL_CHG <> 'Y' and a.TR_JOB_CHG_ACTION ='U'
          AND (a.emplid, a.action_dt_ss, a.effseq) in
          (select b.emplid , b.effdt, b.effseq from ps_job b where
a.emplid = b.emplid and a.action_dt_ss = b.effdt and a.effseq = b.effseq
           and b.ELIG_CONFIG4 = ' ') and exists
(SELECT  emplid,
                        effdt,
                        EFFSEQ,
                        ELIG_CONFIG4 FROM PS_JOB C
                       WHERE C.emplid = a.emplid
                         AND C.ELIG_CONFIG4 <> ' '
                         AND c.effdt = (SELECT MAX(ED.EFFDT) FROM
PS_JOB ED
                                           WHERE ED.EMPLID = c.EMPLID
                                             AND ED.EMPL_RCD =
c.EMPL_RCD
                                             AND ED.EFFDT <
a.action_dt_ss)
                                             AND c.EFFSEQ = (SELECT
MAX(ES.EFFSEQ) FROM PS_JOB ES
                                                                  WHERE
ES.EMPLID = c.EMPLID
 
AND ES.EMPL_RCD = c.EMPL_RCD
 
AND ES.EFFDT = c.EFFDT))

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of mohammadzahid
mohammadzahid
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial