delete
from
    ITEM
where
    (
        ITEM_CODE in
        (
            select
                i.ITEM_CODE as code
            from
                ITEM as i
            left outer join
                STOCK as s on s.ITEM_CODE = i.ITEM_CODE
            where
                s.ITEM_CODE is NULL
        )
        OR EXISTS ( 
            SELECT
                ITEM_CODE 
            FROM
                STOCK 
            WHERE
                STOCK.ITEM_CODE = ITEM.ITEM_CODE 
            -- 商品コードで集計した結果を判定するためグルーピングをおこなう
            GROUP BY
                ITEM_CODE 
            HAVING
               -- 最終出荷日の最大値が６カ月以上前かチェック
               -- 入荷実績があるがまだ出荷実績がない場合は、最終出荷日はNULLなので削除の対象外となる
               --  （最終出荷日がNULLの場合は対象外とするため、ダミーで'2999-12-31'をセット)
                MAX(IFNULL(LAST_DELIVERY_DATE, '2999-12-31')) <= DATE ('2023-01-01') 
                -- 実在庫数の合計がゼロかチェック
                AND SUM(ACTUAL_AMT) = 0
        )
    )
and
    STOCK_MANAGEMENT_TYPE = 1;
    
-- DELETE 
-- select ITEM_CODE
-- FROM
--     ITEM 
-- WHERE
--     -- 在庫管理する商品が対象
--     STOCK_MANAGEMENT_TYPE = 1 
--     AND ( 
--         -- 商品コードが在庫テーブルに存在しないこと確認
--         NOT EXISTS ( 
--             SELECT
--                 ITEM_CODE 
--             FROM
--                 STOCK 
--             WHERE
--                 STOCK.ITEM_CODE = ITEM.ITEM_CODE
--         ) 
--         -- 商品コードが在庫テーブルに存在しているが、削除条件を満たしていることを確認
--         OR EXISTS ( 
--             SELECT
--                 ITEM_CODE 
--             FROM
--                 STOCK 
--             WHERE
--                 STOCK.ITEM_CODE = ITEM.ITEM_CODE 
--             -- 商品コードで集計した結果を判定するためグルーピングをおこなう
--             GROUP BY
--                 ITEM_CODE 
--             HAVING
--               -- 最終出荷日の最大値が６カ月以上前かチェック
--               -- 入荷実績があるがまだ出荷実績がない場合は、最終出荷日はNULLなので削除の対象外となる
--               --  （最終出荷日がNULLの場合は対象外とするため、ダミーで'2999-12-31'をセット)
--                 MAX(IFNULL(LAST_DELIVERY_DATE, '2999-12-31')) <= DATE ('2023-01-01') 
--                 -- 実在庫数の合計がゼロかチェック
--                 AND SUM(ACTUAL_AMT) = 0
--         )
--     );