score:3

Accepted answer

Update ThresholdType instead:

update tablename set ThresholdType = 1 - ThresholdType  

In case other ThresholdType values might show up later, you can add WHERE ThresholdType IN (1, 2), to be a bit safer.

Read More

score:1

You can do:

update t
set ThresholdValue = (
  select x.ThresholdValue
  from t x
  where x.DeviceID = t.DeviceID and x.ThresholdType <> t.ThresholdType
)

Result:

 DeviceID  ThresholdType  ThresholdValue 
 --------- -------------- -------------- 
 1         0              10             
 1         1              5              
 2         0              20             
 2         1              15             

See running example at db<>fiddle.

score:3

Just swap the ThresholdType:

UPDATE t SET ThresholdType = CASE ThresholdType
    WHEN 1 THEN 0
    WHEN 0 THEN 1
    ELSE ThresholdType
END

Execute the query exactly once.


More questions with similar tag