CODEWITHSUNDEEP
sqlsql-server
Sheraz Ahmed
Sheraz Ahmed

Reputation: 453

Conditionally add a column with a default value to an existing table in SQL Server

I want to add a new column in existing SQL table with default values depending upon some cases/conditions.

I want to know if this is possible. If yes how? if not why?

Alternative :

One option is to create the column and then update but approach is out of the question here.

Let’s consider I have the following table 

╔════╦══════════════╗
║ ID ║      Age     ║ 
╠════╬══════════════║
║  1 ║    0.166667  ║   
║  2 ║     0.125    ║   
║  3 ║       13     ║   
║  4 ║       19     ║  
║  5 ║       45     ║  
║  6 ║       59     ║   
║  7 ║       60     ║  
║  8 ║       64     ║ 
╚════╩══════════════╝

Desired output is this :

╔════╦══════════════╦═══════════╗
║ ID ║      Age     ║ AgeGroup  ║
╠════╬══════════════╬═══════════╣
║  1 ║    0.166667  ║   NewBorn ║
║  2 ║     0.125    ║   NewBorn ║
║  3 ║       13     ║   Teen    ║
║  4 ║       19     ║   Teen    ║
║  5 ║       45     ║   Adult   ║
║  6 ║       59     ║   Adult   ║
║  7 ║       60     ║  Elder    ║
║  8 ║       64     ║  Elder    ║
╚════╩══════════════╩═══════════╝

I have studied this post but this is only for "Adding column default values"

EDIT : Here is the SQL script with schema and data for the above table for users who wants to test.

Upvotes: 4

Views: 11010

Answers (4)

DarkRob
DarkRob

Reputation: 3833

You may try this. There are basically 2 approach to achieve this.

Adding Column in table schema

As I understand you want to add a column in table schema as given link shared by you. You may use case statement to add column for calculated values.

EDIT : Correcting Syntax Error

alter table yourtable
    add AgeGroup as (case 
                        when Age < 2 then 'New Born'
                        when Age < 19 then 'Teen'
                        when Age < 45 then 'Young'
                        when Age > 60 then 'Adult'
                        else 'Invalid' 
                     end);

Or

Create view

You may create a view for same and use it wherever you needed.

Create View TableResult 
As
Select Id, Age, case Age 
                         WHEN Age < 2 then 'New Born'
                         WHEN Age < 19 then 'Teen'
                         WHEN Age < 45 then 'Young'
                         WHEN Age > 60 then 'Adult'
                         else 'Invalid' 
                     end as AgeGroup
End

Upvotes: 10

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use case when

        select case when age>0 and age<13 then 'new born'
        when age>=13 and age<=19 then 'teen'
        .................
           ... end -- put here more condition as your need

Upvotes: 4

Max Voisard
Max Voisard

Reputation: 1942

First, you will have to create the AgeGroup column to the table:

ALTER TABLE yourTable
ADD AgeGroup VARCHAR(10)

As for making default values based on set conditions:

UPDATE yourTable
SET AgeGroup = CASE WHEN yourTable.Age >= 0 AND yourTable.Age < 2 THEN 'NewBorn'
WHEN yourTable.Age >= 13 AND yourTable.Age <= 19 THEN 'Teen'
WHEN yourTable.Age >= 20 AND yourTable.Age <= 59 THEN 'Adult'
WHEN yourTable.Age >= 60 THEN 'Elder' END

Upvotes: 1

Zeina
Zeina

Reputation: 1603

You can use a calculated Field.

Your table design will be like this:

 CREATE TABLE [dbo].[MyTbl](    
 [ID] [INT] IDENTITY(1,1) NOT NULL, 
 [Age] DECIMAL(10,3) NOT NULL,  
 [AgeGroup]  AS (CASE WHEN Age < 1 THEN 'Newborn' 
                      WHEN Age BETWEEN 1 AND 13 THEN 'Kid' 
                      WHEN Age BETWEEN 13 AND 60 THEN 'Adult' 
                      WHEN Age > 60 THEN 'Elder' END)  
 CONSTRAINT [PK_ID]
 PRIMARY KEY CLUSTERED  (   [ID] ASC ))

Your field will be filled automatically, you just have to add your age.

Upvotes: 4

Related Questions