Replace Value in Column with Value in Different Column

Question

I have a dataset that looks like this in SQL.

Col1     Col2     Col3     
   A        4        1
   B        5     NULL 
   C        6        1
   D        7     NULL
   E        8     NULL

How do I add a new column with the values in Col2 with the values in Col3 if Col3 = 1, or else keep the existing values in Col2.

Final Expected Output:

Col1     Col2     Col3     Col4     
   A        4        1        1
   B        5     NULL        5
   C        6        1        1
   D        7     NULL        7
   E        8     NULL        8

I tried the coalesce function but I don't think that worked:

SELECT 
Col1, 
Col2, 
Col3,
coalesce(Col3, Col2) AS Col4
FROM table1

Answer 1

Your description suggests a case expression :

select . . . 
       (case when col3 = 1 then col3 else col2 end) as col4

You could also express the above as

select . . . 
       (case when col3 = 1 then 1 else col2 end) as col4

For the data you provided, coalesce() should also work.