具有连接和顺序的Postgres RETURNING子句 [英] Postgres RETURNING clause with join and order

问题描述

我有这个查询，它更新了一些行并在RETURNING子句中返回了更新的行.但是，即使我在内部查询中指定了ORDER BY mycolumn，也不会对RETURNING返回的行进行排序.

I've got this query that updates some rows and returns the updated rows in the RETURNING clause. However, even though I've specified ORDER BY mycolumn in the inner query, the rows returned by RETURNING aren't ordered.

UPDATE mytable SET status = 'A'
FROM
  (
    SELECT id FROM mytable
    WHERE status = 'B'
    ORDER BY mycolumn
    LIMIT 100
    FOR UPDATE
  ) sub
  JOIN jointable j ON j.id = sub.id
WHERE mytable.id = sub.id
RETURNING *

我尝试将ORDER BY放在外部查询中，就像在JOIN之后或WHERE之后一样，但是在两种情况下都出现错误.如何使其按期望的顺序返回行?

I tried putting an ORDER BY in the outer query, like after the JOIN or after the WHERE, but I get an error in both cases. How can I make it return the rows in the desired order?

(在在Postgresql中更新退货单中，回答了类似的问题但这不包括JOIN，仅包括ORDER.)

(A similar question was answered in Update Returning Order by in postgresql but that doesn't include JOINs, only ORDER.)

推荐答案

使用CTE:

WITH updated as(
    UPDATE mytable SET status = 'A'
FROM
  (
    SELECT id FROM mytable
    WHERE status = 'B'
    ORDER BY mycolumn
    LIMIT 100
    FOR UPDATE
  ) sub
  JOIN jointable j ON j.id = sub.id
WHERE mytable.id = sub.id
RETURNING *
)
select *
from updated
ORDER BY mycolumn

这篇关于具有连接和顺序的Postgres RETURNING子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！