PostgreSQL中UPDATE结合子查询时LIMIT不生效问题排查

现象 #

你执行的两个UPDATE语句（IN子查询版和FROM子查询版），设置LIMIT 150后却更新并返回了全部272条符合条件的记录，而你的需求是：选取最多N个唯一的thing_id，更新这些thing_id对应的所有行。

前两种写法的核心问题 #

这两个写法的错误在于LIMIT作用在了原始行上，而非去重后的thing_id集合，完全偏离了你的需求：

1. 子查询SELECT thing.thing_id ... LIMIT 150是对查询结果的行进行数量限制，而不是对thing_id的唯一性进行限制。
   • 若存在同一个thing_id对应多行的情况，LIMIT会返回该thing_id的多条记录，最终IN子句中只会保留这一个thing_id，导致仅更新该thing_id的行，而非N个不同thing_id的所有行。
   • 即便你的测试数据集里每个thing_id仅对应1行，当前写法也无法保证LIMIT稳定作用在唯一thing_id上（若排序字段存在大量重复，优化器可能调整执行逻辑，导致LIMIT未按预期生效）。
2. 外层UPDATE重复添加了与子查询完全一致的过滤条件thing.c_id = ('c_id___1') AND thing.name LIKE ('protocol://some/url/location/%')，属于冗余逻辑，且可能干扰优化器生成正确的执行计划。

正确实现方案 #

要实现“选取最多N个唯一thing_id，更新其所有对应行”的需求，必须先对thing_id去重，再应用LIMIT：

方案1：先去重再LIMIT #

UPDATE thing 
SET status = 'status_2' 
WHERE thing_id IN (
  SELECT DISTINCT thing_id
  FROM thing
  WHERE status = 'status_1' 
    AND c_id = 'c_id___1' 
    AND name LIKE 'protocol://some/url/location/%'
  ORDER BY status_1_date
  LIMIT 150
)
RETURNING *;

方案2：使用窗口函数精确控制排序和选取 #

如果需要更精准的排序逻辑（比如按status_1_date排序后选取前N个thing_id），可以用窗口函数实现：

WITH ranked_thing_ids AS (
  SELECT DISTINCT thing_id,
         ROW_NUMBER() OVER (ORDER BY status_1_date) AS rn
  FROM thing
  WHERE status = 'status_1' 
    AND c_id = 'c_id___1' 
    AND name LIKE 'protocol://some/url/location/%'
)
UPDATE thing
SET status = 'status_2'
WHERE thing_id IN (
  SELECT thing_id
  FROM ranked_thing_ids
  WHERE rn <= 150
)
RETURNING *;

测试数据验证 #

当设置LIMIT 2时，上述方案会先选取前2个唯一的thing_id（thing_1和thing_2），然后更新它们对应的所有5行数据，完全符合你的期望。

关于你尝试的JOIN方案的问题 #

你尝试的JOIN写法SELECT a.thing_id FROM thing a INNER JOIN thing b on b.thing_id = a.thing_id ... LIMIT 150，本质上是无意义的自连接，既没有实现去重，也没有解决LIMIT作用在单行的问题，反而增加了查询成本，且仅在thing_id与行数一一对应时才看似生效，完全不支持有重复thing_id的场景。

内容的提问来源于stack exchange，提问作者Cogito Ergo Sum