WITH AVERAGE_STD AS (
    SELECT
        GOODS_CODE
        , ROUND(AVG(STD_COST_PRICE)) AS AVG_STD_COST
    FROM
        SKU
    GROUP BY
        GOODS_CODE
)
, UPDATE_TARGET AS (
    SELECT  
        SKU.SKU_CODE
        , SKU.GOODS_CODE
        --, SKU.STD_COST_PRICE
        --, AVERAGE_STD.AVG_STD_COST
    FROM
        SKU
    LEFT JOIN
        AVERAGE_STD ON SKU.GOODS_CODE = AVERAGE_STD.GOODS_CODE
    WHERE
        SKU.STD_COST_PRICE < AVG_STD_COST
)
, UPDATE_COST_FROM AS (
    SELECT
        GOODS.GOODS_CODE
        , GOODS.STD_COST_PRICE
    FROM
        GOODS
    WHERE
        GOODS.GOODS_CODE IN (
            SELECT
                DISTINCT UT.GOODS_CODE
            FROM
                UPDATE_TARGET AS UT
        )
)

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

SELECT
    *
FROM
    SKU
;