Increment primary key/unique value on column in MySQL

Question

My goal is to increment of +7 a primary key value into a table in MySQL db.

Tablename = table, Primary key = id

Example:

id   name   surname
1    John   Doe
2    Mary   McCain
3    Sam    Smith
4    Roy    Jenson

I need to turn it into

id   name   surname
8    John   Doe
9    Mary   McCain
10   Sam    Smith
11   Roy    Jenson

So that I can insert 7 more rows before the existing ones.

I tried:

 UPDATE table SET id = id + 7

But I get the error:

 Failed to execute SQL : SQL UPDATE table SET id = id + 7 failed : Duplicate entry '2' for key 1

Honestly I really don't know how to fix this, also because I have more than 122,000 entries in that table and it would take several days if not several weeks to update them one by one by hand.

Answer 1

Just add ORDER BY ID DESC at the end of the update query:

UPDATE table SET id = id + 7 ORDER BY ID DESC;

See this SQLFiddle

Try to remove ORDER BY in the given SQLFiddle, you will get the same error.

Please take the backup before doing so.

---

If you are trying to subtract then you need to use ASC instead of DESC in the ORDER BY clause.

UPDATE table SET id = id - 7 ORDER BY ID ASC;

See this SQLFiddle