How to re-assign the ID colunda again?

Asked

Viewed 48 times

1

I have a table for example

| ID  | NOME |
|  2  | Ana  |
|  7  | João |
|  15 | Vera |

The ID column is ID int NOT NULL AUTO_INCREMENT,

What I need to get?

I intend for the column to stay 

| ID  | NOME |
|  1  | Ana  |
|  2  | João |
|  3  | Vera |

I tried many examples one of them would be to use a php to re-assign to each column field.

There is no way to do this with an SQL command ?

mysql sql

  • It’s relatively simple to do this using UPDATE with @variables and SET, but you’re probably trying to solve a XY problem. Don’t want to better specify your need for a more comprehensive solution?

1 answer

3


If you want a sequential numbering, you don’t need to change the Ids, just use a counter:

SELECT    @linha := @linha + 1 AS contador,
          tabela_desejada.*
FROM      (SELECT @linha := 0) AS nada,
          tabela_desejada
ORDER BY  id;

So you’ll have sequential numbering.

But if you really want to change Ids, you can use the same logic by doing an UPDATE.

However, I understand that it is a terrible solution, and will provide unnecessary maintenance. The sense of the Id field is to be the Identity of the record, not a line counter. If you need a sequential counter, you should probably use a path as suggested above, or a separate column for this.

Anyway, it follows the query:

SET       @contador:=0;
UPDATE    tabela
SET       id=@contador:=@contador+1
ORDER BY  id;

and get ready to fix DB autonumbering on a variety of occasions.

Browser other questions tagged mysql sql

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