将WHERE NOT EXISTS与Informix中的复合主键一起使用

-- 创建表 table1
CREATE TABLE table1 (
    id INT,
    name VARCHAR(50),
    age INT,
    PRIMARY KEY (id, name)
);

-- 创建表 table2
CREATE TABLE table2 (
    id INT,
    name VARCHAR(50),
    address VARCHAR(100),
    PRIMARY KEY (id, name)
);

-- 插入示例数据
INSERT INTO table1 (id, name, age) VALUES (1, 'Alice', 25);
INSERT INTO table1 (id, name, age) VALUES (2, 'Bob', 30);
INSERT INTO table2 (id, name, address) VALUES (1, 'Alice', '123 Main St');

-- 使用 WHERE NOT EXISTS 删除 table1 中在 table2 中存在的记录
DELETE FROM table1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2
    WHERE table1.id = table2.id AND table1.name = table2.name
);

-- 创建索引以优化查询性能
CREATE INDEX idx_table2_id_name ON table2(id, name);

-- 分批删除
DELETE FROM table1
WHERE id IN (
    SELECT id
    FROM table1
    WHERE NOT EXISTS (
        SELECT 1
        FROM table2
        WHERE table1.id = table2.id AND table1.name = table2.name
    )
)
AND id BETWEEN 1 AND 100;

-- 检查并处理重复数据
INSERT INTO table1 (id, name, age)
SELECT t.id, t.name, t.age
FROM (
    SELECT id, name, age
    FROM table1
    WHERE NOT EXISTS (
        SELECT 1
        FROM table2
        WHERE table1.id = table2.id AND table1.name = table2.name
    )
    EXCEPT
    SELECT id, name, age
    FROM table1
) t;