WITH GOODS_CODE_avg as (
    select
        GOODS_CODE
        ,ROUND(avg(STD_COST_PRICE)) as avg_std_cost_price
    FROM
        SKU
    GROUP BY 1
)
,std_cost_price_set as (
    select
        GOODS_CODE
        ,CASE
            WHEN SKU.STD_COST_PRICE < avg_std_cost_price THEN GOODS.STD_COST_PRICE
            ELSE SKU.STD_COST_PRICE
        END as STD_COST_PRICE_SET
        ,CASE
            WHEN SKU.STD_COST_PRICE < avg_std_cost_price THEN 'MODIFY'
            ELSE SKU.UPDATE_USER_NAME
        END as UPDATE_USER_NAME_SET
        ,CASE
            WHEN SKU.STD_COST_PRICE < avg_std_cost_price THEN '2024-08-31 00:00:00'
            ELSE SKU.USER_UPDATE_DATETIME
        END as USER_UPDATE_DATETIME_SET
    FROM
        SKU
        LEFT JOIN GOODS USING(GOODS_CODE)
        LEFT JOIN GOODS_CODE_avg USING(GOODS_CODE)
)
UPDATE
    SKU
SET
    STD_COST_PRICE = (
        SELECT
            STD_COST_PRICE_SET
        FROM
            std_cost_price_set
        WHERE
            SKU.GOODS_CODE = std_cost_price_set.GOODS_CODE
    )
    ,UPDATE_USER_NAME = (
        SELECT
            UPDATE_USER_NAME_SET
        FROM
            std_cost_price_set
        WHERE
            SKU.GOODS_CODE = std_cost_price_set.GOODS_CODE
    )
    ,USER_UPDATE_DATETIME = (
        SELECT
            USER_UPDATE_DATETIME_SET
        FROM
            std_cost_price_set
        WHERE
            SKU.GOODS_CODE = std_cost_price_set.GOODS_CODE
    )
;

SELECT * FROM SKU