IF OBJECT_ID('T_SOURCE') IS NOT NULL
  DROP TABLE T_SOURCE;
IF OBJECT_ID('T_TARGET') IS NOT NULL
  DROP TABLE T_TARGET;

CREATE TABLE T_SOURCE (ID INT, Value NVARCHAR(MAX), ReverceRank INT);
INSERT INTO T_SOURCE VALUES
(1, 'AAA', 1),
(2, 'BBB', 2),
(2, 'CCC', 1),
(3, 'DDD', 3),
(3, 'EEE', 2),
(3, 'FFF', 1);
SELECT * FROM T_SOURCE;

CREATE TABLE T_TARGET (ID INT, Value NVARCHAR(MAX));
INSERT INTO T_TARGET VALUES
(1, 'GGG'),
(2, 'HHH'),
(3, 'III');
SELECT * FROM T_TARGET;

UPDATE TRG
SET Value = SRC.Value
FROM T_TARGET TRG INNER JOIN T_SOURCE SRC ON TRG.ID = SRC.ID;
SELECT * FROM T_TARGET;

UPDATE TRG
SET Value = SRC.Value
FROM T_TARGET TRG INNER JOIN T_SOURCE SRC ON TRG.ID = SRC.ID
WHERE SRC.ReverceRank = 1;
SELECT * FROM T_TARGET;