如何获取SQLite中Upsert时因条件未执行更新的冲突行标识？

要找出存在主键冲突、但因ON CONFLICT DO UPDATE的WHERE条件不满足而未执行更新（也未插入）的行，你可以借助SQLite的WITH公共表表达式（CTE）来实现，同时保证整个操作的原子性。

核心思路 #

1. 先将待插入的数据存入CTE，方便后续复用；
2. 执行Upsert操作并捕获所有实际被插入或更新的行；
3. 通过对比待插入数据、现有表数据和已变更行，筛选出冲突但未更新的行。

具体实现代码 #

WITH input_data(name, phonenumber, validDate) AS (
  -- 替换为你的待插入数据，支持多条记录
  VALUES('Alice','704-555-1212','2018-05-08')
),
changed_rows AS (
  -- 执行Upsert并捕获已变更的行
  INSERT INTO phonebook2(name, phonenumber, validDate)
  SELECT name, phonenumber, validDate FROM input_data
  ON CONFLICT(name) DO UPDATE SET
    phonenumber = excluded.phonenumber,
    validDate = excluded.validDate
  WHERE excluded.validDate > phonebook2.validDate
  RETURNING name
)
-- 查询冲突但未更新的行
SELECT id.name AS conflicted_not_updated_name
FROM input_data id
JOIN phonebook2 pb ON id.name = pb.name
WHERE id.validDate <= pb.validDate
AND id.name NOT IN (SELECT name FROM changed_rows);

代码说明 #

• input_data：存储所有待插入的记录，支持批量处理多条数据；
• changed_rows：执行Upsert操作，并用RETURNING捕获所有被插入或更新的行的name；
• 最后一步查询：通过关联待插入数据和现有表，筛选出满足以下条件的行：
  • name匹配（存在主键冲突）；
  • 待插入的validDate不大于现有行的validDate（不满足更新条件）；
  • 不在已变更行列表中（未被插入/更新）。

同时追踪已变更和冲突未更新的行 #

如果需要同时获取两种状态的记录，可以用UNION ALL合并结果：

WITH input_data(name, phonenumber, validDate) AS (
  VALUES('Alice','704-555-1212','2018-05-08'),
        ('Bob','919-555-1212','2024-01-01')
),
changed_rows AS (
  INSERT INTO phonebook2(name, phonenumber, validDate)
  SELECT name, phonenumber, validDate FROM input_data
  ON CONFLICT(name) DO UPDATE SET
    phonenumber = excluded.phonenumber,
    validDate = excluded.validDate
  WHERE excluded.validDate > phonebook2.validDate
  RETURNING name, '已插入/更新' AS 状态
)
SELECT name, 状态 FROM changed_rows
UNION ALL
SELECT id.name, '冲突但未更新' AS 状态
FROM input_data id
JOIN phonebook2 pb ON id.name = pb.name
WHERE id.validDate <= pb.validDate
AND id.name NOT IN (SELECT name FROM changed_rows);

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