Thread: Renumber table rows

Renumber table rows

From
Efraín Déctor
Date:
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
 

Re: Renumber table rows

From
"David Johnston"
Date:

 

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

 

Re: Renumber table rows

From
Efraín Déctor
Date:
Thank you. It really worked.
 
Sent: Wednesday, June 06, 2012 3:17 PM
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