DELETE FROM ITEM AS A
WHERE (
    -- 在庫テーブル(STOCK)に商品コード(ITEM_CODE)が存在しない
    NOT EXISTS (
        SELECT X.ITEM_CODE
        FROM STOCK X
        WHERE X.ITEM_CODE = A.ITEM_CODE
    )
    OR
    -- 存在しても、商品毎の最終出荷日(LAST_DELIVERY_DATE)の最大値が6カ月以上前で実在庫数(ACTUAL_AMT)がゼロ
    EXISTS (
        SELECT
            X1.ITEM_CODE AS ITEM_CODE,
            X2.MAX_LAST_DELIVERY_DATE AS MAX_LAST_DELIVERY_DATE,
            X1.ACTUAL_AMT AS ACTUAL_AMT
        FROM STOCK X1
        INNER JOIN (
            SELECT
                Y.ITEM_CODE AS ITEM_CODE,
                MAX(Y.LAST_DELIVERY_DATE) AS MAX_LAST_DELIVERY_DATE
            FROM STOCK Y
            GROUP BY Y.ITEM_CODE
        ) X2
        ON X1.ITEM_CODE = X2.ITEM_CODE
        WHERE X2.MAX_LAST_DELIVERY_DATE <= DATE('2022-12-01')   -- 2023年6月1日の6ヶ月前
        AND X1.ACTUAL_AMT = 0
    )
)
-- 商品データの在庫管理区分(STOCK_MANAGEMENT_TYPE) = 1 (在庫管理する)のデータのみ対象
AND A.STOCK_MANAGEMENT_TYPE = 1