How to update based on case statement results?

• craig-404139

  Hall of Fame

  Points: 3274

  More actions

  August 21, 2008 at 10:35 am

  #861411

  craig (8/21/2008)

  ---

  Not sure where im going wrong here, but I adjusted your code slightly to fit my needs. But when I try to run the update statement I recieve the error "Msg 4145, Level 15, State 1, Line 6

  An expression of non-boolean type specified in a context where a condition is expected, near ')'. I also recieved this error with your orginal code, any ideas?

  -- Generate test data

  Create TABLE People(person_id INT IDENTITY(1,1), dob smalldatetime)

  Create TABLE Benefits(person_id INT, dedyrmax VARCHAR(5), age smallint)

  DECLARE @m INT, @y INT

  SET @y = 76

  WHILE @y > 0

  BEGIN

  SET @m = 12

  WHILE @m > 4

  BEGIN

  INSERT INTO People (dob)

  SELECT

  DATEADD(DAY, (@y - @m)/3, DATEADD(MONTH, -@m, DATEADD(YEAR, -@y, GETDATE())))

  SET @m = @m - 4 * RAND(4)

  END

  SET @y = @y - 3 * RAND(7)

  IF @y = 0

  BEGIN

  SET @y = 1

  END

  END

  -- insert birthday after todays date

  INSERT INTO people (dob)

  SELECT

  '10/7/1980'

  UNION ALL

  SELECT

  '8/21/1980'

  INSERT INTO benefits(Person_id)

  SELECT

  person_id

  FROM People

  -- Do the update

  UPDATE Benefits

  SET dedyrmax = CASE

  WHEN CASE

  WHEN MONTH(P.dob)>MONTH(GETDATE()) OR DAY(P.dob)>DAY(GETDATE()) THEN DATEDIFF(YEAR, P.dob, GETDATE()) - 1

  ELSE DATEDIFF(YEAR, P.dob, GETDATE())

  END) >= 50 THEN '50000'

  ELSE dedyrmax

  END,

  Dumb me, I found my error.

  -- Do the update

  UPDATE Benefits

  SET dedyrmax = CASE

  WHEN CASE

  WHEN MONTH(dob) >MONTH(GETDATE()) OR DAY(P.dob)>DAY(GETDATE()) THEN DATEDIFF(YEAR, P.dob, GETDATE()) - 1

  ELSE DATEDIFF(YEAR, P.dob, GETDATE())

  END >= 50 THEN 'Sr'

  ELSE 'Jr'

  END

  FROM

  people as p inner JOIN

  benefits as e ON

  p.person_Id = e.person_id