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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Efraín Déctor 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 |
