Update data using SET / WHERE statements

Question

Is there a way to update these records using a WHERE statement?

UPDATE HRBIQuery SET HRBIQuery.PaySegmentMultiplier = '1.35', 
    HRBIQuery.PaySegmentMultiplier = '1.25', HRBIQuery.PaySegmentMultiplier = '1.15',
    HRBIQuery.PaySegmentMultiplier = '.90', HRBIQuery.PaySegmentMultiplier = '.60',
    HRBIQuery.PaySegmentMultiplier = '.40' 
WHERE (HRBIQuery.BasePayRangeSegment = 'Below segment 1' AND HRBIQuery.PayGroupCountryDesc = 'France',
    HRBIQuery.BasePayRangeSegment = 'S1' AND HRBIQuery.PayGroupCountryDesc = 'France',
    HRBIQuery.BasePayRangeSegment = 'S2' AND HRBIQuery.PayGroupCountryDesc = 'France',
    HRBIQuery.BasePayRangeSegment = 'S3' AND HRBIQuery.PayGroupCountryDesc = 'France',
    HRBIQuery.BasePayRangeSegment = 'S4' AND HRBIQuery.PayGroupCountryDesc = 'France',
    HRBIQuery.BasePayRangeSegment = 'S5' AND HRBIQuery.PayGroupCountryDesc = 'France');

Thanks!

Answer 1

You've got a couple options here.
Option 1: use a switch statement in your query.

UPDATE HRBIQuery SET HRBIQuery.PaySegmentMultiplier =
  switch(HRBIQuery.BasePayRangeSegment = 'Below segment 1',1.35,
  HRBIQuery.BasePayRangeSegment = 'Below segment 1',1.35,
  HRBIQuery.BasePayRangeSegment = 'S1',1.25,
  HRBIQuery.BasePayRangeSegment = 'S2',1.15,
  HRBIQuery.BasePayRangeSegment = 'S3',.90,
  HRBIQuery.BasePayRangeSegment = 'S4',.60,
  HRBIQuery.BasePayRangeSegment = 'S5',.40)

WHERE HRBIQuery.BasePayRangeSegment IN('Below segment 1','S1','S2','S3','S4','S5') 
  AND HRBIQuery.PayGroupCountryDesc = 'France'    

Option 2: one query using a nested IIF statement.

UPDATE HRBIQuery SET HRBIQuery.PaySegmentMultiplier =
  IIF(HRBIQuery.PaySegmentMultiplier='Below segment 1',1.35,
  IIF(HRBIQuery.PaySegmentMultiplier='S1',1.25,
  IIF(HRBIQuery.PaySegmentMultiplier='S2',1.15,
  IIF(HRBIQuery.PaySegmentMultiplier='S3,.90,
  IIF(HRBIQuery.PaySegmentMultiplier='S4',.60,
  IIF(HRBIQuery.PaySegmentMultiplier='S1',.40))))))

WHERE HRBIQuery.BasePayRangeSegment IN('Below segment 1','S1','S2','S3','S4','S5') 
  AND HRBIQuery.PayGroupCountryDesc = 'France'

Option 3: use 6 different queries. I won't type all six out. Hopefully you get the idea.

UPDATE HRBIQuery SET HRBIQuery.PaySegmentMultiplier = 1.35
WHERE HRBIQuery.BasePayRangeSegment  = 'Below segment 1'
  AND HRBIQuery.PayGroupCountryDesc = 'France'

Option 4: Use a custom function. Create a public function and call it in your query.

UPDATE HRBIQuery SET HRBIQuery.PaySegmentMultiplier = custom_function(HRBIQuery.BasePayRangeSegment)

WHERE HRBIQuery.BasePayRangeSegment IN('Below segment 1','S1','S2','S3','S4','S5') 
  AND HRBIQuery.PayGroupCountryDesc = 'France'

the custom function should something like(sorry for any syntax errors, but you should get the idea):

public function custom_function(value)
  dim return_value as double 
  select case value
     case 'Below segment 1'
       return_value = 1.35
     case 's1'
       return_value = 1.25
     case 'S2'
       return_value = 1.15
     case 'S3'
       return_value = .90
     case 'S4'
       return_value = .60
     case 'S5'
       return_value = .40
  End select
  return return_value
end function