update multiple fields using case statement

  • This is how update from works - it can cause obscure bugs in production code.

    You have to make sure that only one row is joined, something like:

    UPDATE R

    SET prm_proc = D.prm_proc

    ,fnl_proc1 = D.fnl_proc1

    ,fnl_proc2 = D.fnl_proc2

    FROM smsdss.lcs_T2_reports_new R

    JOIN

    (

    SELECT T.PT_id

    ,MAX(CASE WHEN T.seq_no = 1 THEN T.proc_cd END) AS prm_proc

    ,MAX(CASE WHEN T.seq_no = 2 THEN T.proc_cd END) AS fnl_proc1

    ,MAX(CASE WHEN T.seq_no = 3 THEN T.proc_cd END) AS fnl_proc2

    FROM #temp_PC9_seq T

    GROUP BY T.PT_id

    ) D

    ON R.PT_id = D.PT_id

Viewing post 1 (of 2 total)

You must be logged in to reply to this topic. Login to reply