We help IT Professionals succeed at work.

Need to turn a Select SQL into an update

dmissel
dmissel asked
on
Medium Priority
511 Views
Last Modified: 2012-08-13
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

Comment
Watch Question

Put entire sql statement in inline view and select only the record needed in UPDATE statement.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
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".
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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.