0

I have 3 columns of data: Age, Gender, Points. Age is age, gender is either M or F, and Points range from 1-100.

How do I make a new column so that if gender is M you add 5 points to whatever is in the Points column, and if gender is F you subtract 3 points from whatever is in the Points column?

I think I'm going to need CASE. I've added a new column called PointsRevised by using ALTER TABLE, then add.

Now, I want to populate this new PointsRevised column with new values depending if gender is M or F.

UPDATE [Example].[dbo].[example]
SET POINTSREVISED
CASE 
WHEN Gender = 'M' Then PointsRevised = Points + 5  
WHEN Gender = 'F' Then PointsRevised = Points - 3
END

I'm clearly doing something wrong.

MrPatterns
  • 4,184
  • 27
  • 65
  • 85
  • 1
    do you want to do it just this once, and never again, or is this something that you want to do regularly? – digitalfrenchfry Feb 07 '13 at 22:38
  • I do not know much about MS SQL, but it seems like you are trying to do something which belongs in a view to an actual data table. The only way to accomplish this would be with some very interesting feature of MS SQL allowing for a column to have a computed value which essentially acts like a view. – le3th4x0rbot Feb 07 '13 at 22:55
  • @digitalfrenchfry once, and never again. thanks. out of curiosity, from a high-level overview, what changes if I choose to it regularly? – MrPatterns Feb 08 '13 at 17:01
  • 1
    @phan for example, if its something you wanted it to happen every day, you can create a stored procedure that loops itself every 24 hours. – digitalfrenchfry Feb 08 '13 at 21:22

1 Answers1

6

This will work:

UPDATE [Example].[dbo].[example]
SET POINTSREVISED =
  CASE 
  WHEN Gender = 'M' THEN Points + 5  
  WHEN Gender = 'F' THEN Points - 3
  END

Simpler:

UPDATE [Example].[dbo].[example]
SET POINTSREVISED =
  CASE Gender
  WHEN 'M' THEN Points + 5  
  WHEN 'F' THEN Points - 3
  END

If the only valid values for Gender is 'M' and 'F' (and it can't be NULL), you could replace the above WHEN 'F' THEN with simply ELSE.

Depending on your requirements, a computed column may work better.

Community
  • 1
  • 1
Bernhard Barker
  • 54,589
  • 14
  • 104
  • 138