dmissel
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))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.