-- STEP1: 商品コード(GOODS_CODE)ごとに標準原価(STD_COST_PRICE)の平均を算出し、平均よりも低い標準原価のSKUを選択
WITH AVG_COSTS AS (
    SELECT
        GOODS_CODE,
        ROUND(AVG(STD_COST_PRICE), 1) AS AVG_STD_COST_PRICE
    FROM SKU
    GROUP BY GOODS_CODE
)

-- STEP2: 平均標準原価よりも小さい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.STD_COST_PRICE < (
    SELECT AVG_COSTS.AVG_STD_COST_PRICE
    FROM AVG_COSTS
    WHERE AVG_COSTS.GOODS_CODE = SKU.GOODS_CODE
);

SELECT * FROM SKU
ORDER BY GOODS_CODE	ASC, SPEC1_CODE ASC, SPEC2_CODE ASC;