UPDATE on all records, changing autoincrement number

0

I have about 500 records in the database, with autoincrement on a table starting at 900,901,902 onwards, would I have to do an UPDATE on all records starting autoincrement at number 502?

Then all records have ID autoincrement 502,503,504, then?

    
asked by anonymous 22.11.2017 / 21:49

2 answers

0
SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;

Resolved

    
22.11.2017 / 22:00
0
SHOW CREATE TABLE tabela;
[COPIE O CODIGO E EXECUTE APOS O COMANDO DO RENAME]
RENAME TABLE tabela TO tabela_old;
INSERT INTO tabela (
[DESCREVA TODOS OS CAMPOS MENOS O ID]
)
SELECT
    [TODOS OS CAMPOS MENOS O ID]
FROM tabela_old;

Make sure the data is correct and delete the old table.

I prefer to do this, so I do not run a direct update to the table and lose some data. and also advise to make a backup of the data and check if this table you are changing does not reference with any other, otherwise you will lose references

    
24.11.2017 / 19:49