SQL: using case when to change column values

Question

I am creating new columns but in particular I want to say if a value of a column is equal to a value, then change a corresponding column. For example:

SELECT
     Col1 as c1
    ,Col2 as c2
    ,CASE CAST([COB] as varchar(50))
        WHEN 'Engineering' then set c1 = 1
        ELSE 0
    END AS [Class of Business]
FROM
     ....

but I can't get it to work, for instance if a entry in the column [COB] was 'engineering' then I want it to set (on the same row) the column c1 to 0, else just fill the column c1 with 0's

Answer 1

If you only want to query your table, you can return 1 or 0 in base of COB status without reassign to c1 the new value.

Try this:

SELECT
CASE 
    WHEN CAST([COB] as varchar(50)) = 'Engineering' then 1
    ELSE Col1
END AS c1,
Col2 as c2
FROM ...