|Value|
|7.5 |
|9 |
|15.5 |
|12.5 |
|6.5 |
|9 |
The Table has lots of numerical values. I want to change all the rows that have 9 to 8.5 and 10.5. If there are 8 values of 9, then 8.5 and 10.5 should be inserted equally and randomly
You could use window functions:
select
value,
case rn % 2
when 0 then 8.5
when 1 then 10.5
else value
end new_value
from (
select
value,
case when value = 8
then row_number() over(partition by value order by rand())
end rn
from mytable
) t
The inner query randomy ranks records that have value 8 - note that the order by rand() syntax is not consistent across databases (eg it is supported by MySQL, while SQLServer wants order by newid()).
The outer query assigns new values: even ranks get value 8.5, and uneven ranks get 10.5.
The subquery is actually just there to shorten the syntax in the outer query, but is not strictly necessary:
select
value,
case
when value = 8 and row_number() over(partition by value order by rand()) % 2 = 0
then 8.5
when value = 8
then 10.5
else value
end new_value
from mytable