Applying trigger or functionality to a new column

• Minnesota - Viking

  Hall of Fame

  Points: 3469

  More actions

  June 16, 2010 at 11:08 am

  #222872

  I have a table like

  Name Age Salary Bonus

  1000 500

  Question 1

  I want a new column that has only four options to choose from

  a.High(Bonus >500)

  b.Medium(300<Bonus <500)

  c.Low(100<Bonus <300)

  d.Meager(Bonus < 100)

  How do i add this new column to my table with 50,000 rows already present.

  O/P shd look like

  Name Age Salary Bonus Type

  Tom 34 1000 555 High

  Tom 34 1000 50 Meager

  Tom 34 1000 255 Low

  Tom 34 1000 355 Medium

  Can any type of such functionality be added as a new column or we shd have done that while creating the table itself ?

  Like we do in Excel.. we add new column and apply a formula to it..

  So how do i do that in SQL Server

  [font="Verdana"]
  Today is the tomorrow you worried about yesterday:-)[/font]

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  June 16, 2010 at 11:20 am

  #1181187

  1 - Add new column. Check "alter table add column" syntax.

  2 - Write an update query to implement business logic.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• only4mithunc

  SSCrazy Eights

  Points: 8434

  More actions

  June 16, 2010 at 11:27 am

  #1181189

  You can do it like this

  ALTER TABLE tablename ADD columnname AS CASE WHEN Bonus < 100 THEN 'Meager'

  WHEN Bonus between 100 and 300 THEN 'Low'

  WHEN Bonus between 300 and 500 THEN 'Medium'

  WHEN Bonus > 500 THEN 'High' END

  Thanks & Regards,
  MC

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  June 16, 2010 at 8:53 pm

  #1181338

  only4mithunc (6/16/2010)

  ---

  You can do it like this

  ALTER TABLE tablename ADD columnname AS CASE WHEN Bonus < 100 THEN 'Meager'

  WHEN Bonus between 100 and 300 THEN 'Low'

  WHEN Bonus between 300 and 500 THEN 'Medium'

  WHEN Bonus > 500 THEN 'High' END

  Nice code..seeing update like this the first time.. 😎