PostgreSQL Record Reordering using Update with a Sub-Select

Question

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

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

ERROR: table name "sometable" specified more than once

Any help will be appreciated.

Thanks!

Reordering rows and setting the value in a column (which is what this query apparently tries to do) are two different things. — Mike Sherrill 'Cat Recall', Jul 17 '11 at 18:31

maniek · Accepted Answer · 2011-07-17T18:46:02.137

5

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

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

edited Jul 17 '11 at 18:46

answered Jul 17 '11 at 18:35

maniek

6,727
2
19
41

PostgreSQL Record Reordering using Update with a Sub-Select

1 Answers1

Linked