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

Tag your question with the database you are using. `COALESCE()` should do what you want. — Gordon Linoff, Apr 13 '18 at 12:15
Left justified SQL is very hard to read. Proper formatting makes everything much easier. — jarlh, Apr 13 '18 at 12:18

score 1 · Accepted Answer · edited Apr 13 '18 at 12:18

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.

edited Apr 13 '18 at 12:18

Yogesh Sharma

49,870
5
26
52

answered Apr 13 '18 at 12:16

Gordon Linoff

1,242,037
58
646
786

thanks, I'll try this out in a couple hours and let you know! This looks like it should work, appreciate the help – nak5120 Apr 13 '18 at 12:17
In my example, does it look like I am using coalesce right? – nak5120 Apr 13 '18 at 12:17
@NickKnauer . . . Yes. You are using it correctly. – Gordon Linoff Apr 13 '18 at 12:18

score 0 · Answer 2 · answered Apr 13 '18 at 12:17

0

Try this:

SELECT 
    Col1, 
    Col2, 
    Col3,
    CASE WHEN Col3 IS NOT NULL THEN Col3 ELSE Col2 END AS Col4
FROM table1

answered Apr 13 '18 at 12:17

DineshDB

5,998
7
33
49

score 0 · Answer 3 · answered Apr 13 '18 at 12:17

0

-- this should do what you want
SELECT 
Col1, 
Col2, 
Col3,
CASE WHEN Col3 = 1 THEN Col3 ELSE Col2 END AS NewCOL
FROM table1

answered Apr 13 '18 at 12:17

Brad

3,454
3
27
50

score 0 · Answer 4 · answered Apr 13 '18 at 12:25

0

Insert into table2
select 
Col1, 
Col2, 
Col3, 
(case when col3 = 1 then 1 else col2 end) as col4
FROM table1

answered Apr 13 '18 at 12:25

WorksOnMyLocal

1,617
3
23
44

2

A bit of elaboration on why this works would help future people with the same issue understand the solution. – KevinO Apr 13 '18 at 17:08

Replace Value in Column with Value in Different Column

4 Answers4