MYSQL in phpmyadmin uniqueid primarykey duplicate id numbers

Question

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.

Answer 1

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  |

Answer 2

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