子表插入数据并更新父表外键的SQL查询方案咨询

老哥，你要的这种把父表Name值批量插入子表，再把生成的子表ID回写到父表TranslationId的需求，完全不用写额外的程序，直接用SQL语句就能搞定！我给你整理了几种常用数据库的方案，都是可以直接执行的：

SQL Server 方案（一步到位） #

SQL Server的OUTPUT子句刚好能完美适配这个场景，只用两步就能完成插入+更新：

-- 声明临时表，用来存插入的子表ID和对应的父表原始ID
DECLARE @InsertedIds TABLE (ParentId INT, ChildId INT);

-- 批量插入子表，同时把关联关系存到临时表
INSERT INTO 子表名称 (De)
OUTPUT inserted.Id, p.Id INTO @InsertedIds(ChildId, ParentId)
SELECT Name FROM 父表名称 WHERE TranslationId IS NULL;

-- 根据临时表的关联关系，更新父表的TranslationId字段
UPDATE p
SET p.TranslationId = i.ChildId
FROM 父表名称 p
JOIN @InsertedIds i ON p.Id = i.ParentId;

说明： #

• 把父表名称和子表名称替换成你实际的表名就行
• 临时表@InsertedIds用来记录每一条父表记录对应的子表ID，确保关联关系不会错
• 这个方案只适合SQL Server，因为它支持OUTPUT子句捕获插入后的字段值

MySQL 方案（批量处理） #

MySQL没有OUTPUT子句，但我们可以用自增主键的特性来实现，步骤也很简单：

-- 创建临时表存储父表ID和子表ID的对应关系
CREATE TEMPORARY TABLE IF NOT EXISTS InsertedIds (ParentId INT, ChildId INT);

-- 先把父表的Name值批量插入子表的De列
INSERT INTO 子表名称 (De)
SELECT Name FROM 父表名称 WHERE TranslationId IS NULL;

-- 计算每个父表记录对应的子表ID（子表ID是连续自增的）
INSERT INTO InsertedIds (ParentId, ChildId)
SELECT 
    p.Id, 
    (SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = '子表名称') - 1 + ROW_NUMBER() OVER (ORDER BY p.Id)
FROM 父表名称 p
WHERE p.TranslationId IS NULL;

-- 更新父表的TranslationId字段
UPDATE 父表名称 p
JOIN InsertedIds i ON p.Id = i.ParentId
SET p.TranslationId = i.ChildId;

-- 可选：用完临时表可以删掉它
DROP TEMPORARY TABLE IF EXISTS InsertedIds;

说明： #

• 要求子表的Id是自增主键，这个是关键
• 这里通过查询INFORMATION_SCHEMA获取子表插入前的自增起始值，再结合ROW_NUMBER()来匹配每个父表记录对应的子表ID，即使父表ID不连续也能正常工作

PostgreSQL 方案（用RETURNING子句） #

PostgreSQL可以用RETURNING子句替代SQL Server的OUTPUT，用CTE（公共表表达式）来实现一步更新：

WITH inserted AS (
    -- 插入子表并返回生成的ID和对应的父表ID
    INSERT INTO 子表名称 (De)
    SELECT Name FROM 父表名称 WHERE TranslationId IS NULL
    RETURNING Id, (SELECT Id FROM 父表名称 WHERE Name = inserted.De) AS ParentId
)
-- 用CTE里的关联关系更新父表
UPDATE 父表名称 p
SET TranslationId = inserted.Id
FROM inserted
WHERE p.Id = inserted.ParentId;

注意： #

如果父表的Name字段有重复值，上面的WHERE Name = inserted.De会出问题，这时候可以调整CTE的逻辑，比如用ROW_NUMBER来关联：

WITH parent_data AS (
    SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY Id) AS rn
    FROM 父表名称 WHERE TranslationId IS NULL
),
inserted AS (
    INSERT INTO 子表名称 (De)
    SELECT Name FROM parent_data ORDER BY rn
    RETURNING Id, ROW_NUMBER() OVER (ORDER BY Id) AS rn
)
UPDATE 父表名称 p
SET TranslationId = inserted.Id
FROM parent_data pd
JOIN inserted ON pd.rn = inserted.rn
WHERE p.Id = pd.Id;

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