如何在SQL插入时通过子查询取值并用返回ID更新同表

你写的SQL存在两个核心问题导致无法运行：

• 语法错误：INSERT ... VALUES 中直接嵌套子查询时，需要给子查询包裹括号，且更推荐用INSERT ... SELECT的写法代替VALUES传子查询，兼容性更好
• 作用域错误：INSERT ... RETURNING返回的结果无法直接被后续独立的UPDATE语句引用，两个独立SQL语句之间的变量不互通；另外两步拆分执行如果没有事务和行锁保护，并发场景下会出现链表指针错乱的问题。

根据你用的数据库类型选择对应方案即可，两种方案都能保证操作原子性，避免并发问题。

PostgreSQL 方案（支持CTE写操作的数据库都适用） #

用可写CTE把插入、更新逻辑合并为单条SQL，数据库会原子执行整段逻辑，不需要额外定义变量：

WITH inserted_node AS (
  INSERT INTO tableList(randomCol1, next_id)
  SELECT 'string 4', next_id 
  FROM tableList 
  WHERE unique_id = 4
  FOR UPDATE -- 对目标节点加行锁，避免并发插入同一位置导致指针覆盖
  RETURNING unique_id AS new_id
)
UPDATE tableList
SET next_id = inserted_node.new_id
FROM inserted_node
WHERE tableList.unique_id = 4;

执行逻辑说明：

1. 先锁定unique_id=4的目标行，读取它当前的next_id值
2. 插入新行，新行的randomCol1为string 4，next_id为刚才读取到的原目标行的next_id，插入完成后返回新生成的自增ID
3. 直接用返回的新ID更新原目标行的next_id，完成链表节点插入

不支持CTE写操作的数据库方案（如MySQL 5.x等） #

用事务+用户变量实现，执行时会锁定目标行直到事务提交，同样可以保证数据一致性：

BEGIN;
-- 锁定目标节点，同时读取它当前的next_id存入变量
SELECT next_id INTO @original_next FROM tableList WHERE unique_id = 4 FOR UPDATE;
-- 插入新节点
INSERT INTO tableList(randomCol1, next_id) VALUES('string 4', @original_next);
-- 获取刚插入节点生成的自增ID
SET @new_node_id = 2129712;
-- 更新原目标节点的指针指向新节点
UPDATE tableList SET next_id = @new_node_id WHERE unique_id = 4;
COMMIT;

• 不要把插入、更新拆分成两个无事务保护的独立SQL执行，高并发场景下如果多个请求同时向同一个节点后插入新数据，会出现指针覆盖，导致链表断裂、数据丢失
• 对插入位置的目标节点加行锁是必须的，否则会出现脏写问题

内容的提问来源于stack exchange，提问作者era s'q