使用带有子选择的更新的 PostgreSQL 记录重新排序 [英] PostgreSQL Record Reordering using Update with a Sub-Select

问题描述

我在 SQL Server 论坛上找到了有关如何对表中的记录重新排序的解决方案.

I found this solution on the SQL Server forum on how to reorder records in a table.

UPDATE SomeTable
SET rankcol = SubQuery.Sort_Order
FROM
    (
    SELECT IDCol, Row_Number() OVER (ORDER BY ValueCOL) as SORT_ORDER
    FROM SomeTable
    ) SubQuery
INNER JOIN SomeTable ON
SubQuery.IDCol = SomeTable.IDCol

当我尝试在 PostgreSQL 上做同样的事情时，我收到一条错误消息 -

When I try doing the same on PostgreSQL, I get an error message -

错误:多次指定表名sometable"

ERROR: table name "sometable" specified more than once

任何帮助将不胜感激.

谢谢！

推荐答案

你不需要显式加入 SomeTable，这有多酷?:)

You don`t need to explicitly join SomeTable, how cool is that? :)

UPDATE SomeTable
SET rankcol = SubQuery.Sort_Order
FROM
    (
    SELECT IDCol, Row_Number() OVER (ORDER BY ValueCOL) as SORT_ORDER
    FROM SomeTable
    ) SubQuery
where SubQuery.IDCol = SomeTable.IDCol

备注:Postgres 不区分大小写，最好使用小写，如 row_number、sort_order、id_col 等

remark: Postgres is case insensitive, better use lower-case, like row_number, sort_order, id_col , etc.

这篇关于使用带有子选择的更新的 PostgreSQL 记录重新排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！