UPDATE in all records, changing autoincrement number

Asked

Viewed 743 times

0

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

Then all records will have ID autoincrement 502,503,504, onwards ?

mysql

2 answers

1


SET  @num := 0;

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

ALTER TABLE your_table AUTO_INCREMENT =1;

Solved

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 way, not to run a direct update on table and lose some data. and also advise to take a backup of the data and check if that table you are changing does not reference with any other if you will not lose references

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.