How do I use CASE in SQL for my example?

问题:

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.

回答1:

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.