Update col value based on rule

Question

I have a table with a value and a colrank column, value is the value of a specific id, and rank is the percentage of a specific identifier in whole records.

I want to update records in two conditions, such as

• where the colrank value is greater than 0.95, then the update value corresponds to the value of the closest matching colrank value <= 0.95

• where colrank is less than 0.5, and then update the value to a value close to the colrank value corresponding to a value> 0.5

  declare @tbl table (id int , value float , colrank float );
  
  insert into @tbl
  values(1,10, 0.52),
        (2,11, 0.48),
        (3,18, 0.94),
        (4,22, 0.96);
  
  select * from @tbl;
  

my code is below, but I am not satisfied, because I use the same table several times. Please suggest that this is the best way.

;WITH CTE AS 
(
SELECT MAX(colrank ) high 
FROM @tbl 
where   colrank <= 0.95
), 
cte1 as (
SELECT min(colrank ) low 
FROM @tbl 
where   colrank <= 0.5
)  

select * from cte ,cte1 

Answer 1

, :

SELECT MAX(CASE WHEN colrank <= 0.95 THEN colrank END) high,
       MIN(CASE WHEN colrank <= 0.5 THEN colrank END) low 
FROM @tbl;

DEMO

Answer 2

. , , , , : sql fiddle

create table tbl (id int , value float , colrank float );
insert into tbl
values(1,10, 0.52),
      (2,11, 0.54),
      (3,12, 0.48),
      (4,13, 0.49),
      (5,17, 0.93),
      (6,18, 0.94),
      (7,22, 0.96);

WITH CTE AS
( (SELECT TOP 1 *,1 as [rank] from tbl WHERE colrank < 0.95 ORDER BY colrank DESC)
   UNION
  (SELECT TOP 1 *,2 as [rank] from tbl WHERE colrank > 0.50 ORDER BY colrank ASC)
 )
  SELECT 
    b.id, b.value, b.colrank, a.value
  FROM 
    tbl b 
  LEFT JOIN cte a
  ON 
    (CASE WHEN b.colrank < 0.5 and a.[rank] = 2 THEN 1
        WHEN b.colrank > 0.95 and a.[rank] = 1 THEN 1
    END) = 1
  WHERE a.value IS NOT NULL

select :

WITH CTE AS
( (SELECT TOP 1 *,1 as [rank] from tbl WHERE colrank < 0.95 ORDER BY colrank DESC)
   UNION
  (SELECT TOP 1 *,2 as [rank] from tbl WHERE colrank > 0.50 ORDER BY colrank ASC)
 )
  UPDATE b
  SET b.value = a.value
  FROM 
    tbl b 
  LEFT JOIN cte a
  ON 
    (CASE WHEN b.colrank < 0.5 and a.[rank] = 2 THEN 1
        WHEN b.colrank > 0.95 and a.[rank] = 1 THEN 1
    END) = 1
  WHERE a.value IS NOT NULL

Answer 3

In this case, simple will work UPDATEwith CASE. Try the query as follows.

UPDATE T
SET T.Value =
   (
      CASE WHEN T.colrank > 0.95 THEN (SELECT TOP 1 VALUE FROM @tbl T1
                         WHERE T1.colrank <= 0.95 ORDER BY T1.colrank DESC)
      WHEN T.colrank < 0.5 THEN (SELECT TOP 1 VALUE FROM @tbl T2 
                         WHERE T2.colrank >= 0.5 ORDER BY T2.colrank)
      ELSE T.value
      END
   )
FROM @tbl T

Demo

Answer 4

These are two updates, but they are still effective.

declare @tbl table (id int , value float , colrank float );
insert into @tbl
values(1, 10, 0.52),
      (2, 11, 0.48),
      (3, 18, 0.94),
      (4, 22, 0.96);
declare @min float = (select min(colrank) from @tbl where colrank >= 0.5);
declare @max float = (select max(colrank) from @tbl where colrank <= 0.95);
select * from @tbl;
update @tbl set colrank = @min where colrank < @min;
update @tbl set colrank = @max where colrank > @max;
select * from @tbl;

id          value                  colrank
----------- ---------------------- ----------------------
1           10                     0.52
2           11                     0.48
3           18                     0.94
4           22                     0.96

id          value                  colrank
----------- ---------------------- ----------------------
1           10                     0.52
2           11                     0.52
3           18                     0.94
4           22                     0.94