re-enumerate the primary key with auto incrementmysql attribute

Navigation
0

I have a BD with 278 records that import from excel in CSV format, the problem is that I started to number the id from 278 onwards now what I want is to number them correlatively from 1 ... it is worth mentioning that they are primary key auto increment

    
asked by 04.07.2017 в 17:41
source

2 answers

1

There is really a very simple trick you can implement. 

SELECT @razondecambio:=(MIN(id) - 1) FROM tabla; -- @razondecambio:=277
UPDATE tabla SET id=id-@razondecambio;           -- 1er registro: id=(278-271)=1
SELECT @maximo:=MAX(id) FROM tabla;           
ALTER TABLE tabla AUTO_INCREMENT = @maximo + 1;

You will notice that it is really a game:

  • queries as the lowest value of your self-increasing field.
  • you update each value taking into account the one obtained previously, any value above it runs the risk of being duplicated and given that as it is a primary key, this would be a problem.
  • queries which is the maximum current value of your new numbering.
  • you update your table counter with an increase over the current maximum.

    • I hope you find it very useful.

        
    answered by 05.07.2017 в 04:00
    0

    you must update the AUTO-INCREMENT of the table 

    ALTER TABLE tu_tabla AUTO_INCREMENT = 1;
        
    answered by 04.07.2017 в 17:53
    Error editing XML file using PHP form capture variable in a mysql trigger