Set primary keys in sequence

Question

I have the table empresas as an example:

cod | nome
----|----------
3   | Asterix
7   | Lunar
13  | Coimbra

Fortunately the table of companies currently has no reference in another table, I would like to be able to put the numbers of the primary keys (column cod) in sequence leaving the table as below:

cod | nome
----|----------
1   | Asterix
2   | Lunar
3   | Coimbra

Then I’d have to make one update in all records, I know that if I start from the registry with the lowest value key and continue up to the highest value key I will not have key breach problem, I wonder if this is possible.

Initially I will do this in a database Firebird, but if someone posts an answer on postgresql or sql-server will have my vote.

Answer 1

Here’s a solution for SQL Server:

-- código #1
with empresas_seq as (
SELECT seq= row_number() over (order by cod), nome
  from bd1..empresas
)
INSERT into bd2..empresas (cod, nome)
  SELECT seq, nome 
    from empresas_seq;

---

To update in the current table:

-- código #2 v2
USE bd1;

with empresas_seq as (
SELECT cod, seq= row_number() over (order by cod), nome
  from empresas
)
UPDATE empresas_seq
  set cod= seq;

Here’s another solution to upgrade, more traditional:

-- código #3
with empresas_seq as (
SELECT cod, seq= row_number() over (order by cod), nome
  from empresas
)
UPDATE E
  set cod= ES.seq
  from empresas as E
       inner join empresas_seq as ES on ES.cod = E.cod;