CODEWITHSUNDEEP
mysqlsql-update
Gurdt
Gurdt

Reputation: 181

MySQL duplicate 'ID' = 0 and cant put AUTO_INCREMENT on

I'm busy with transferring an old website to new hosting. Now it's almost done, but there is something wrong with the database some primary keys don't go AUTO_INCREMENT. So now I have a lot of duplicated IDs that are all 0.

Is there a way in SQL that I can update all id's where = 0 to plus those? The last ID which is filled good is "2108" so the next id needs to be "2109" and after that ++ them.

SELECT * FROM `x9i8Z_yoast_seo_links` WHERE `id` = 0

Please help me out

Upvotes: 1

Views: 226

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28864

Pre MySQL 8.0.2 solution would be using User-defined variables. Since ordering is not important in your case, we can determine a sequential row number value directly (without worrying about the order). It will be starting from the "last good id" value + 1. We will then update the id value for the rows, where id value is 0.

UPDATE `x9i8Z_yoast_seo_links` AS t
CROSS JOIN (SELECT @rn := 2108) AS user_init -- 2108 is your "last good id" 
SET t.id = (@rn := @rn + 1)
WHERE t.id = 0

Demo - Schema (MySQL v5.7)

create table test
(id int);

insert into test 
values (1),(2),(3),(4),(0),(0),(0),(0);

Query #1: Data before update operation

select * from test;

| id  |
| --- |
| 1   |
| 2   |
| 3   |
| 4   |
| 0   |
| 0   |
| 0   |
| 0   |

Query #2: Update operation is done

update test AS t
cross join (select @rn := 4) AS user_init 
set t.id = (@rn := @rn + 1)
where t.id = 0;

Query #3: Data after the update operation is done

select * from test;

| id  |
| --- |
| 1   |
| 2   |
| 3   |
| 4   |
| 5   |
| 6   |
| 7   |
| 8   |

View on DB Fiddle

Upvotes: 2

Related Questions