WITH AvgCost AS (
    SELECT GOODS_CODE, ROUND(AVG(STD_COST_PRICE), 0) AS Avg_Price
    FROM GOODS
    GROUP BY GOODS_CODE
)
UPDATE GOODS
SET STD_COST_PRICE = CASE
    WHEN STD_COST_PRICE < (SELECT Avg_Price FROM AvgCost WHERE AvgCost.GOODS_CODE = GOODS.GOODS_CODE)
    THEN (SELECT Avg_Price FROM AvgCost WHERE AvgCost.GOODS_CODE = GOODS.GOODS_CODE)
    ELSE STD_COST_PRICE
END;

UPDATE SKU
SET STD_COST_PRICE = (
    SELECT G.STD_COST_PRICE
    FROM GOODS AS G
    WHERE G.GOODS_CODE = SKU.GOODS_CODE
),
    UPDATE_USER_NAME = 'MODIFY',
    USER_UPDATE_DATETIME = '2024-08-31 00:00:00';

SELECT * FROM SKU