MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (`color` VARCHAR(6), `rank` INTEGER);

INSERT INTO table1 (`color`, `rank`) VALUES
  ('Red', '1'),
  ('Blue', '2'),
  ('Green', '3'),
  ('Yellow', '4'),
  ('White', '5'),
  ('Black', '6');

CREATE TABLE table2 (`id` VARCHAR(3), `ColorValue` VARCHAR(19));

INSERT INTO table2 (`id`, `ColorValue`) VALUES
  ('ID1', 'Red,Yellow'),
  ('ID2', 'Green'),
  ('ID3', 'White'),
  ('ID4', 'Green,Red'),
  ('ID5', 'Yellow, White,Black');
SELECT DISTINCT t2.id, t2.ColorValue, 
       FIRST_VALUE(t1.color) OVER (PARTITION BY t2.ColorValue ORDER BY t1.`rank`) Value_to_be_replaced
FROM table2 t2 INNER JOIN table1 t1
ON FIND_IN_SET(t1.color, t2.ColorValue)
ORDER BY t2.id


id
ColorValue
Value_to_be_replaced


ID1
Red,Yellow
Red

ID2
Green
Green

ID3
White
White

ID4
Green,Red
Red

ID5
Yellow, White,Black
Yellow


SELECT t2.*,
       (
         SELECT t1.color
         FROM table1 t1
         WHERE FIND_IN_SET(t1.color, t2.ColorValue)
         ORDER BY t1.`rank` LIMIT 1
       ) Value_to_be_replaced
FROM table2 t2
ORDER BY t2.id


id
ColorValue
Value_to_be_replaced


ID1
Red,Yellow
Red

ID2
Green
Green

ID3
White
White

ID4
Green,Red
Red

ID5
Yellow, White,Black
Yellow

id	ColorValue	Value_to_be_replaced
ID1	Red,Yellow	Red
ID2	Green	Green
ID3	White	White
ID4	Green,Red	Red
ID5	Yellow, White,Black	Yellow