My goal is to increment of +7 a primary key value into a table in MySQL db.
我的目标是将主键值+7增加到MySQL数据库中的表中。
Tablename = table, Primary key = id
Tablename = table,主键= 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.
这样我就可以在现有行之前再插入7行。
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.
老实说,我真的不知道如何解决这个问题,因为我在该表中有超过122,000个条目,如果不是几个星期,手动逐个更新它们需要几天时间。
1 个解决方案
#1
13
Just add ORDER BY ID DESC at the end of the update query:
只需在更新查询结束时添加ORDER BY ID DESC:
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.
尝试在给定的SQLFiddle中删除ORDER BY,您将得到相同的错误。
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.
如果您试图减去那么您需要在ORDER BY子句中使用ASC而不是DESC。
UPDATE table SET id = id - 7 ORDER BY ID ASC;
See this SQLFiddle
#1
13
Just add ORDER BY ID DESC at the end of the update query:
只需在更新查询结束时添加ORDER BY ID DESC:
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.
尝试在给定的SQLFiddle中删除ORDER BY,您将得到相同的错误。
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.
如果您试图减去那么您需要在ORDER BY子句中使用ASC而不是DESC。
UPDATE table SET id = id - 7 ORDER BY ID ASC;