对于MySQL 8.x，我们可以在col3= val1 or val2中使用递增1的运行和来对数据进行分组，然后在nullif函数中使用条件max window函数来只获得val1和null而不是val2。获得正确的结果（使用CTE）后，使用带有联接的更新来更新表。

with cte as
(
  select col1, 
    nullif(max(case when col3 in ('Val1', 'Val2') then col3 else null end) over (partition by grp), 'Val2') der 
  from(
    select *,
    sum(col3 in ('Val1', 'Val2')) over (order by col1) grp
    from table_name
  ) t
)
update table_name tb join cte 
  on tb.col1 = cte.col1
  set derivied_col = cte.der

demo

UPDATE your_table t1
SET
    t1.Derived1 = 
        CASE
            WHEN t1.Col3 = 'Val1' THEN 'Val1'
            WHEN t1.Col3 = 'Val2' THEN NULL
            ELSE t1.Derived1
        END,
    t1.Derived2 = 
        CASE
            WHEN t1.Col3 = 'Val1' THEN 'Val1'
            WHEN t1.Col3 = 'Val2' THEN NULL
            ELSE t1.Derived2
        END,
    t1.Derived3 = 
        CASE
            WHEN t1.Col3 = 'Val1' THEN 'Val1'
            WHEN t1.Col3 = 'Val2' THEN NULL
            ELSE t1.Derived3
        END
WHERE 
    t1.Col3 IN ('Val1', 'Val2') OR 
    (t1.Derived1 = 'Val1' OR t1.Derived2 = 'Val1' OR t1.Derived3 = 'Val1')
ORDER BY t1.Col1, t1.Col2;