Renumber table rows
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
Try something along the lines of:
UPDATE operador SET idoperador = new_idoperador
FROM (
SELECT idoperador AS old_idoperador, ROW_NUMBER() OVER (ORDER BY idoperador) AS new_idoperador
FROM operador
) lookup
WHERE operador.idoperador = lookup.old_idoperador;
The basic ideas is to create a lookup table and use it in via a FROM clause attached to the UPDATE.
David J.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Efraín Déctor
Sent: Wednesday, June 06, 2012 2:27 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Renumber table rows
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
Thank you. It really worked.
From: David Johnston
Sent: Wednesday, June 06, 2012 3:17 PM
To: 'Efraín Déctor' ; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Renumber table rows
Try something along the lines of:
UPDATE operador SET idoperador = new_idoperador
FROM (
SELECT idoperador AS old_idoperador, ROW_NUMBER() OVER (ORDER BY idoperador) AS new_idoperador
FROM operador
) lookup
WHERE operador.idoperador = lookup.old_idoperador;
The basic ideas is to create a lookup table and use it in via a FROM clause attached to the UPDATE.
David J.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Efraín Déctor
Sent: Wednesday, June 06, 2012 2:27 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Renumber table rows
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