[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

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

0
dmissel
Asked:
dmissel
  • 2
3 Solutions
 
mohammadzahidCommented:
Put entire sql statement in inline view and select only the record needed in UPDATE statement.

0
 
germanklfCommented:
Basically you should do this refactor:

UPDATE targetTable
SET <fields = new-values-from-the-other-table>
FROM PhysicalTargetTable targetTable
INNER JOIN PhysicalSourceTable sourceTable ON <conditions>
WHERE <conditions>

It computes the intersection of both tables and modifies every record matching in "targetTable".
0
 
germanklfCommented:
Sorry for the prior post... my bad.

Basically you should do the refactor i've attached in the code snippet...

It computes the intersection of both tables and modifies every record matching in "targetTable".
Basically you should do this refactor:
 
UPDATE targetTable
SET [fields = new-values-from-the-other-table]
FROM PhysicalTargetTable targetTable
INNER JOIN PhysicalSourceTable sourceTable ON [conditions]
WHERE [conditions]

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now