WITH avggenka AS ( --平均標準原価を算出
    SELECT
        t0.GOODS_CODE,
        ROUND(AVG(CASE WHEN t1.STD_COST_PRICE IS NULL THEN t0.STD_COST_PRICE ELSE t1.STD_COST_PRICE END), 1) AS genkaAvg
    FROM GOODS AS t0
    LEFT OUTER JOIN SKU AS t1
        ON t0.GOODS_CODE = t1.GOODS_CODE
    GROUP BY t0.GOODS_CODE
),
target_skus AS ( --更新対象の商品を抽出
    SELECT DISTINCT
        t1.GOODS_CODE,
        t1.SKU_CODE,
        t1.STD_COST_PRICE,
        ta.genkaAvg
    FROM SKU AS t1
    INNER JOIN SKU AS t0
        ON t1.GOODS_CODE = t0.GOODS_CODE
    INNER JOIN avggenka AS ta
        ON t1.GOODS_CODE = ta.GOODS_CODE
        AND t1.STD_COST_PRICE < ta.genkaAvg
)
-- SKUデータの標準原価を更新
UPDATE SKU
SET 
    STD_COST_PRICE = (
        SELECT g.STD_COST_PRICE
        FROM GOODS g
        WHERE g.GOODS_CODE = SKU.GOODS_CODE
    ),
    UPDATE_USER_NAME = 'MODIFY',
    USER_UPDATE_DATETIME = '2024-08-31 00:00:00'
WHERE SKU_CODE IN (
    SELECT SKU_CODE
    FROM target_skus
);