Update multiple fields using case statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update multiple fields using case statement

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2009-10-22 : 10:42:09

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 05:08:32

This happens because youre case statements are missing the ELSE.
Since there is no ELSE your case statements for T2.prm_proc and T2.fnl_proc1 are set to null when the third case is working.

The ELSE should always set the column to its own value like this example:
T2.prm_proc =
CASE
WHEN PRC.seq_no = '1'
THEN PRC.proc_cd
ELSE T2.prm_proc
END

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-23 : 13:40:06

actually you want this i guess


UPDATE t2
SET t2.prm_proc=prc.prm_proc,
t2.fnl_proc1=prc.fnl_proc1,
t2.fnl_proc2=prc.fnl_proc2
FROM smsdss.lcs_T2_reports_new t2	
INNER JOIN (SELECT pt_id,
            MAX(CASE WHEN seq_no=1 THEN  prc_code ELSE NULL END) AS prm_proc,
            MAX(CASE WHEN seq_no=2 THEN  prc_code ELSE NULL END) AS fnl_proc1,
            MAX(CASE WHEN seq_no=3 THEN  prc_code ELSE NULL END) AS fnl_proc2
            FROM #temp_PC9_seq
            GROUP BY pt_id)prc
ON prc.pt_id=t2.pt_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources