WITH avg_code AS(
    SELECT
        goods_code,
        round(avg(std_cost_price)) as avg_price
    FROM
        SKU
    GROUP BY
        goods_code
        )
UPDATE
    SKU AS S
SET
    update_user_name = 'MODIFY',
    user_update_datetime = '2024-08-31 00:00:00',
    std_cost_price = case
                        WHEN S.std_cost_price >= avg_price then S.std_cost_price
                        ELSE G.std_cost_price
                     end
FROM
    avg_code AS A
LEFT JOIN
    GOODS AS G
ON
    A.goods_code = G.goods_code
WHERE
    S.goods_code = A.goods_code
    AND S.goods_code = G.goods_code;