clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798731 fiddles created (41859 in the last week).

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');
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)