WITH avg_std_cost AS (
    SELECT 
        GOODS_CODE,
        ROUND(AVG(STD_COST_PRICE)) AS AVG_COST
    FROM 
        SKU
    GROUP BY 
        GOODS_CODE
),
sku_to_update AS (
    SELECT 
        s.SKU_CODE,
        s.GOODS_CODE,
        s.STD_COST_PRICE AS OLD_COST,
        g.STD_COST_PRICE AS NEW_COST
    FROM 
        SKU s
    JOIN 
        avg_std_cost a ON s.GOODS_CODE = a.GOODS_CODE
    JOIN 
        GOODS g ON s.GOODS_CODE = g.GOODS_CODE
    WHERE 
        s.STD_COST_PRICE < a.AVG_COST
)
UPDATE SKU
SET 
    STD_COST_PRICE = (
        SELECT NEW_COST 
        FROM sku_to_update 
        WHERE sku_to_update.SKU_CODE = SKU.SKU_CODE
    ),
    UPDATE_USER_NAME = 'MODIFY',
    USER_UPDATE_DATETIME = '2024-08-31 00:00:00'
WHERE 
    SKU_CODE IN (SELECT SKU_CODE FROM sku_to_update);