--SKU 更新
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 G.GOODS_CODE,G.STD_COST_PRICE as STD_COST_PRICE
    from GOODS as G
        join (
            select 
                GOODS_CODE,
                ROUND(AVG(STD_COST_PRICE),1) as Average
            from SKU
            group by GOODS_CODE
            ) as AVe
    where G.STD_COST_PRICE < AVe.Average
    group by G.GOODS_CODE
    )as sub
where 
    SKU.GOODS_CODE = sub.GOODS_CODE;

--更新者と時刻のセット
--SPEC1
update SPEC1
set UPDATE_USER_NAME = 'MODIFY',USER_UPDATE_DATETIME = '2024-08-31 00:00:00'
from (
    select SPEC1_CODE,SPEC2_CODE
    from SKU
    where UPDATE_USER_NAME = 'MODIFY'
        and USER_UPDATE_DATETIME = '2024-08-31 00:00:00'
    )as sub
where SPEC1.SPEC1_CODE=sub.SPEC1_CODE;
--SPEC2
update SPEC2
set UPDATE_USER_NAME = 'MODIFY',USER_UPDATE_DATETIME = '2024-08-31 00:00:00'
from (
    select SPEC1_CODE,SPEC2_CODE
    from SKU
    where UPDATE_USER_NAME = 'MODIFY'
        and USER_UPDATE_DATETIME = '2024-08-31 00:00:00'
    )as sub
where SPEC2.SPEC2_CODE=sub.SPEC2_CODE;