SQL求助：如何仅在idTable2存在时向Table3插入行并批量执行

1. 单条插入时跳过Table2不存在的情况 #

把原来的INSERT...VALUES写法改成INSERT...SELECT，通过关联查询自动过滤Table2无匹配记录的场景：

INSERT INTO table3 (table1id, table2id)
SELECT t1.id, t2.id
FROM table1 t1
JOIN table2 t2 ON t2.guid = 'guidTable2'
WHERE t1.name = 'name1';

只有当Table2中存在对应guid的记录时，才会生成可插入的行，自然跳过无匹配的情况。

2. 批量执行插入（避免报错） #

如果要批量处理多组name和guid，用CTE（公共表表达式）构造待处理数据集，再关联两张表筛选有效记录：

WITH batch_tasks AS (
    -- 替换为你的批量数据，每组一行
    SELECT 'name1' AS target_name, 'guidTable2' AS target_guid UNION ALL
    SELECT 'name2' AS target_name, 'guidTable5' AS target_guid UNION ALL
    SELECT 'name3' AS target_name, 'guidTable7' AS target_guid
)
INSERT INTO table3 (table1id, table2id)
SELECT t1.id, t2.id
FROM batch_tasks bt
JOIN table1 t1 ON bt.target_name = t1.name
JOIN table2 t2 ON bt.target_guid = t2.guid;

这种写法只会插入Table1和Table2都存在匹配记录的行，不会因单条数据缺失导致整个批量操作失败。

3. 记录不存在的Table2行信息 #

如果需要留存缺失的guid记录，先创建一个日志表（若不存在）：

CREATE TABLE IF NOT EXISTS missing_table2_guids (
    id INT AUTO_INCREMENT PRIMARY KEY,
    related_name VARCHAR(255),
    missing_guid VARCHAR(255),
    record_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

然后在批量处理时，将缺失记录插入日志表：

WITH batch_tasks AS (
    SELECT 'name1' AS target_name, 'guidTable2' AS target_guid UNION ALL
    SELECT 'name2' AS target_name, 'guidTable5' AS target_guid UNION ALL
    SELECT 'name3' AS target_name, 'guidTable7' AS target_guid
)
INSERT INTO missing_table2_guids (related_name, missing_guid)
SELECT bt.target_name, bt.target_guid
FROM batch_tasks bt
JOIN table1 t1 ON bt.target_name = t1.name
LEFT JOIN table2 t2 ON bt.target_guid = t2.guid
WHERE t2.id IS NULL;

若不想创建表，也可直接查询输出缺失记录：

WITH batch_tasks AS (
    SELECT 'name1' AS target_name, 'guidTable2' AS target_guid UNION ALL
    SELECT 'name2' AS target_name, 'guidTable5' AS target_guid UNION ALL
    SELECT 'name3' AS target_name, 'guidTable7' AS target_guid
)
SELECT bt.target_name AS 关联Table1名称, bt.target_guid AS 缺失的Table2GUID
FROM batch_tasks bt
JOIN table1 t1 ON bt.target_name = t1.name
LEFT JOIN table2 t2 ON bt.target_guid = t2.guid
WHERE t2.id IS NULL;

补充说明 #

• 若Table1的name也可能不存在，将JOIN table1改为LEFT JOIN table1，并在WHERE条件中添加t1.id IS NOT NULL，过滤无效的name。
• 上述写法兼容MySQL 8.0+、PostgreSQL等支持CTE的数据库；若使用MySQL 5.x，可将CTE替换为临时表或子查询。

内容的提问来源于stack exchange，提问作者Kry Zoh