phpmyadmin Question posted in
The official documentation can be found here.

MYSQL in phpmyadmin uniqueid primarykey duplicate id numbers

I have multiple tables that don’t have indexes nor primary key after migration.
There are 9149 entries in some tables and about 10 new entries have been given id = 0.
I can’t give primary key due to multiple entries with same id, and I can’t edit with click due to no primary key. I don’t know what to do.

The highest existing post id number is 9149.
Can someone please give me rewrite these with sequential numbers from 9150 upwards.

Tags:

2

Answers


  1. 0

    This should be as simple as :

    ALTER TABLE wp_posts ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

    This command adds a column named id as the first column in the table, and makes it the primary key. As the column has auto increment option enabled, MySQL will automatically assign a number to each record.

    Here is a demo on DB Fiddle that demonstrates the principle :

    create table mytable (val int);

insert into mytable values(50);
insert into mytable values(50);
insert into mytable values(51);

alter table mytable add id int not null auto_increment primary key first;

select * from mytable;

| id  | val |
| --- | --- |
| 1   | 50  |
| 2   | 50  |
| 3   | 51  |
    Login or Signup to reply.
  2. 0

    Here I use a variable to hold the max id and then use that variable to update rows with id = 0

    SET @rownum = (SELECT MAX(id) FROM test)

UPDATE test SET id = 
  (SELECT @rownum  := (@rownum + 1))
WHERE id = 0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search