CODEWITHSUNDEEP
sql
doug
doug

Reputation: 231

Populate New Column with Existing Column Based On Criteria

I am selecting into an existing column C from column A if it has a value greater than zero. If not then I want to populate it with column B. It will populate with column A only if column B is zero. If both column A and B have values it always uses B. Any help would be appreciated. 

( 
   CASE
       WHEN column A > 0  THEN column A
       ELSE column B                
   END
)

My insert into was correct but my select had the columns reversed which was causing what I thought was an error. Thanks for all of your help. What a noob thing to do.

Upvotes: 1

Views: 12432

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171559

Assuming you want to update the value permanently and not just change it during select, do:

update MyTable
set ColumnC = CASE        
        WHEN ColumnA > 0 THEN ColumnA        
        ELSE ColumnB                
    END

Upvotes: 2

Justin Pihony
Justin Pihony

Reputation: 67135

Your database of choice will be extremely useful, as it looks like you are already correct in most RDBMS. Just stick exactly what you have already inside of a select:

--Insert based on select
--INSERT INTO [TABLETOINSERT] (ColumnToInsert)
SELECT 
    CASE 
       WHEN ColumnA > 0 THEN ColumnA
       ELSE ColumnB
    END AS NewColumn
--Or create a table the select
--INTO [NEWTABLENAME]
FROM [TABLENAME]

Or update:

UPDATE [TABLENAME]
SET NewColumn = 
    CASE 
       WHEN ColumnA > 0 THEN ColumnA
       ELSE ColumnB
    END

Upvotes: 2

Related Questions