Pairwise swapping of rows in sql-CodePudding

I have a device settings table with threshold types and values. Let's say ThresholdType = 0 is Min and ThresholdType = 1 is Max The initial table looks like this:

DeviceID    ThresholdType  ThresholdValue
1           0              5  
1           1              10
2           0              15  
2           1              20
3           0              21  
3           1              34
4           0              1 
4           1              8

Then I had to change ThresholdType meaning - 0 became Max threshold and 1 became Min one.

I want the table look like that:

DeviceID    ThresholdType  ThresholdValue
1           0              10  
1           1              5
2           0              20  
2           1              15
3           0              34  
3           1              21
4           0              8 
4           1              1

Is it possible to change update it with a single SQL without loops?

CodePudding user response：

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.

CodePudding user response：

Update ThresholdValue instead:

update tablename set ThresholdValue = 1 - ThresholdValue

CodePudding user response：

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.