Renumber table rows

From: Efraín Déctor <efraindector(at)motumweb(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Renumber table rows
Date: 2012-06-06 18:27:07
Message-ID: C957BB304B7A4BF8B5F6437A078A43CE@CMOTUM25PC
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello.

I have a table that his primary key is not ordered is something like this:

1 - a
12 - b
123 - c
etc.

I want to do an update to make it like this

1 – a
2 – b
3 – c

I tried this:

UPDATE operador SET idoperador=(SELECT row_number() OVER (ORDER BY idoperador) from operador)

But it returns this error: more than one row returned by a subquery used as an expression and I know that is because the substring returns more than one row, but how can I use the subquery to perform the update?.

Thank you in advance

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2012-06-06 18:31:36 Re: pg_database_size differs from df -s
Previous Message Julien Rouhaud 2012-06-06 16:46:25 Re: pg_database_size differs from df -s