update multiple fields using case statement

• scottcabral

  Default port

  Points: 1495

  More actions

  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

• Ken McKelvey

  SSCoach

  Points: 18938

  More actions

  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