DELETE FROM
    ITEM AS I
WHERE
    --在庫管理区分が１のデータのみ対象
    STOCK_MANAGEMENT_TYPE = 1
     --在庫テーブルに商品コードが存在しない)
    AND ( I.ITEM_CODE NOT IN(
                SELECT
                    ITEM_CODE
                FROM
                    STOCK AS S
                WHERE
                    I.ITEM_CODE = S.ITEM_CODE
                    )
        --存在するが、条件に合致する
        OR I.ITEM_CODE IN (
                SELECT
                    ITEM_CODE
                FROM
                    STOCK AS S
                WHERE
                    I.ITEM_CODE = S.ITEM_CODE
                GROUP BY
                    S.ITEM_CODE
                HAVING
                    MAX(IFNULL(LAST_DELIVERY_DATE, '3000-12-31')) <= DATE('2023-01-01', '-6 month')
                    AND SUM(S.ACTUAL_AMT) = 0
                    )
            );