SQL Case Statement Based on Column value-CodePudding

The 'patient' table needs to be updated from the 'history' table. The patient table has all of the history in a single row while the history table has one type of history per row. How can I use a conditional something like below (my best attempt) to update the right column based on the history_code value which indicates what type of history data it is.

Any help would be appreciated

UPDATE AUS.dbo.patient SET 

    CASE 
    WHEN history_code = 'surgery' THEN surh = smh.condition
    WHEN history_code = 'social' THEN soch = smh.condition
    WHEN history_code = 'medical' THEN medh = smh.condition
    WHEN history_code = 'family' THEN famh = smh.condition
    WHEN history_code = 'ocular' THEN obsh = smh.condition
    END

FROM history AS smh
LEFT JOIN patient AS sp
    ON smh.patient_id = sp.patient_id
LEFT JOIN AUS.dbo.u_master AS um
    ON sp.old_patient_id = um.filenumber COLLATE Latin1_General_CS_AS
WHERE patient.fk_master_masterid = um.masterID```

CodePudding user response：

CASE is an expression not a statement and as such can only return a scalar value, not conditionally run a statement. Instead you need one CASE per column you wish to update, either modifying the value or leaving it the same as per your conditions. Here is one column:

surh =
    CASE
    WHEN history_code = 'surgery' THEN smh.condition
    ELSE surh
    END

I highly recommend reading the documentation when unsure about some functionality.