Adjust MySQL ID Column +1

Question

I have about 1000 lines and I want to increase all line IDs by 1. I thought something like this might work

UPDATE table

SET id = id+1

I tried in phpMyAdmin but got this: Duplicate entry "2" for key "PRIMARY"

It makes sense, but how can I get around this?

Answer 1

Isn't it a lot easier to order it back?

 update table set id = id +1 order by id desc 

it works for me ...

Answer 2

Follow these steps:

• displays a temporary table
• copy all data to this temporary table with the desired identifier
• delete source table
• At the end, rename the temp table to the original table.

What all.

Answer 3

use a large enough number that will not duplicate

 update table set id = id + 1000001; update table set id = id - 1000000; 

remember that this will increase the auto increment key if you use one