SQL技术问询：用插入返回ID批量更新表及外键约束前置处理

针对你提出的两个数据库操作需求，我分别给出适配主流数据库的具体实现方案，都是简洁高效的写法：

需求1：插入TableB后用返回的ID更新TableA所有行 #

这个需求的关键是捕获插入TableB后的ID，然后批量更新TableA。不同数据库的实现略有差异：

MySQL/MariaDB #

如果是插入单条记录到TableB，直接用680491获取自增ID，再更新：

-- 插入数据到TableB
INSERT INTO TableB (column1, column2) VALUES ('val1', 'val2');
-- 捕获插入后的自增ID
SET @new_b_id = 680491;
-- 更新TableA所有行的对应字段
UPDATE TableA SET b_id = @new_b_id;

如果是插入多条记录但需要用最后一条的ID，680491返回第一条插入记录的ID，最后一条ID可通过@new_b_id + ROW_COUNT() - 1计算；若只是要统一更新TableA到同一个ID，用上面的写法就够了。

PostgreSQL #

PostgreSQL的RETURNING子句可以直接捕获插入的ID，结合CTE可以一步完成插入+更新：

WITH inserted_b AS (
  INSERT INTO TableB (column1, column2) VALUES ('val1', 'val2')
  RETURNING id -- 返回插入的ID
)
UPDATE TableA SET b_id = (SELECT id FROM inserted_b);

如果插入多条记录，想指定用某一个ID，调整子查询即可（比如取第一条SELECT id FROM inserted_b LIMIT 1）。

SQL Server #

用OUTPUT子句捕获插入的ID，存入变量后再更新：

DECLARE @new_b_id INT;

-- 插入并捕获ID
INSERT INTO TableB (column1, column2)
OUTPUT INSERTED.id INTO @new_b_id
VALUES ('val1', 'val2');

-- 更新TableA所有行
UPDATE TableA SET b_id = @new_b_id;

如果是批量插入，也可以用临时表存储所有插入的ID，再按需更新。

需求2：为campaigns表添加statistics外键并设置NOT NULL约束 #

这个需求的核心是批量生成与campaigns行数一致的statistics记录，并一一关联，完全不需要循环，用SQL的集合操作就能搞定：

步骤1：批量插入statistics记录 #

直接用INSERT ... SELECT语法，从campaigns表读取行数，生成对应数量的statistics记录：

-- 假设statistics表只有自增id和其他允许默认值的列
INSERT INTO statistics (column1, column2)
SELECT DEFAULT, DEFAULT FROM campaigns;

如果statistics表需要填充特定字段，比如从campaigns同步数据，直接在SELECT里指定即可，比如：

INSERT INTO statistics (campaign_name, created_at)
SELECT c.name, NOW() FROM campaigns c;

步骤2：关联campaigns与新插入的statistics记录 #

这里用行号匹配的方式，把每个campaigns对应到一条新插入的statistics：

MySQL/MariaDB（8.0+支持窗口函数）

UPDATE campaigns c
JOIN (
  -- 给campaigns生成行号
  SELECT id, ROW_NUMBER() OVER () AS row_num FROM campaigns
) c_rows ON c.id = c_rows.id
JOIN (
  -- 给最新插入的N条statistics生成行号（N=campaigns行数）
  SELECT id, ROW_NUMBER() OVER () AS row_num 
  FROM statistics
  ORDER BY id DESC LIMIT (SELECT COUNT(*) FROM campaigns)
) s_rows ON c_rows.row_num = s_rows.row_num
SET c.statistic_id = s_rows.id;

如果是MySQL 5.x版本，用变量生成行号即可：

SET @row_num = 0;
UPDATE campaigns c
JOIN (SELECT id, @row_num := @row_num + 1 AS row_num FROM campaigns) c_rows ON c.id = c_rows.id
JOIN (SELECT id, @row_num := 0 AS dummy, @row_num := @row_num + 1 AS row_num FROM statistics ORDER BY id DESC LIMIT (SELECT COUNT(*) FROM campaigns)) s_rows ON c_rows.row_num = s_rows.row_num
SET c.statistic_id = s_rows.id;

PostgreSQL

WITH campaign_rows AS (
  SELECT id, ROW_NUMBER() OVER () AS row_num FROM campaigns
),
statistic_rows AS (
  SELECT id, ROW_NUMBER() OVER () AS row_num
  FROM statistics
  ORDER BY id DESC LIMIT (SELECT COUNT(*) FROM campaigns)
)
UPDATE campaigns c
SET statistic_id = s.id
FROM campaign_rows cr
JOIN statistic_rows s ON cr.row_num = s.row_num
WHERE c.id = cr.id;

SQL Server

WITH campaign_rows AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM campaigns
),
statistic_rows AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY id DESC) AS row_num
  FROM statistics
  ORDER BY row_num
  OFFSET 0 ROWS FETCH NEXT (SELECT COUNT(*) FROM campaigns) ROWS ONLY
)
UPDATE c
SET statistic_id = s.id
FROM campaigns c
JOIN campaign_rows cr ON c.id = cr.id
JOIN statistic_rows s ON cr.row_num = s.row_num;

步骤3：添加NOT NULL约束和外键 #

完成关联更新后，就可以添加约束了：

-- 先添加NOT NULL约束（注意不同数据库语法略有差异）
-- MySQL/MariaDB
ALTER TABLE campaigns MODIFY COLUMN statistic_id INT NOT NULL;
-- PostgreSQL
ALTER TABLE campaigns ALTER COLUMN statistic_id SET NOT NULL;
-- SQL Server
ALTER TABLE campaigns ALTER COLUMN statistic_id INT NOT NULL;

-- 再添加外键约束
ALTER TABLE campaigns
ADD CONSTRAINT fk_campaigns_statistics
FOREIGN KEY (statistic_id) REFERENCES statistics(id);

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