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!

that's not going to work. you're setting the same field to multiple different values, and the where clause is a flat-out syntax error. you mean you want to update 6 different records with those 6 different values? — Marc B, Sep 08 '15 at 21:42
Correct, I want to update the 6 different records based on 6 different conditions — Chris2015, Sep 08 '15 at 21:45
then run 6 separate updates. it'd make for a very ugly update to test/set the 6 values separately. — Marc B, Sep 08 '15 at 21:55
You could use a nested iif to do it in one query, but it's pretty ugly. You could also use an IN statement in your where instead of 5 separate where criteria. Your second option is to create a custom function which you pass BasePayRangeSegment to, then it would return the appropriate value based off a simple case statement. Third option would be to run 6 different queries. — Gene, Sep 08 '15 at 23:47

Gene · Accepted Answer · 2015-09-09T10:56:03.187

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

Update data using SET / WHERE statements

1 Answers1