Post reply

update multiple fields using case statement

  • October 22, 2009 at 8:53 am

    #100688

    Hi,

    i need to update a table using a case statement. The table being updated is joined to the table with the values used to update the main table, which in this case 3 rows are returned. However, when i update the main table fields, only the last row is being used for updated.

    here is the code:

    ****************************************

    UPDATE

    T2

    SET

    T2.prm_proc =

    CASE

    WHEN PRC.seq_no = '1'

    THEN PRC.proc_cd

    END

    , T2.fnl_proc1 =

    CASE

    WHEN PRC.seq_no = '2'

    THEN PRC.proc_cd

    END

    , T2.fnl_proc2 =

    CASE

    WHEN PRC.seq_no = '3'

    THEN PRC.proc_cd

    END

    FROM

    smsdss.lcs_T2_reports_new T2

    JOIN

    #temp_PC9_seq PRC

    ON T2.pt_id = PRC.pt_id

    ***************************

    PRC table values:

    pt_id seq_no prc_code

    1 1 59.22

    1 2 60.22

    1 3 61.22

    T2 table values

    pt_id prm_proc fnl_proc1 fnl_proc2

    1 null null 31.22

    it's almost like the case statment only used the last row (sequence 3) of the PRC table to update. Any idea why the other 2 fields are not being updated?

    thanks

    Scott

  • October 22, 2009 at 9:45 am

    #1069501

    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 2 posts - 1 through 1 (of 1 total)

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