7

I have a MySQL 5 server and a table in it with an autoincrement on an id field (primary key). Now I want to add a record in between and so I have to increase all other ids by one. This is what I tried:

UPDATE myTable SET id=id+1 WHERE id >= 53

This doesn't work because for example a record with id = 52 already exists. How can I do this? If he would start at the last entry and makes the updates it should work I think. But how?

testing
  • 19,681
  • 50
  • 236
  • 417
  • 3
    The primary is intended to provide an *identity* for a row, not to *order* them. Use a separate column to specify your desired order. Changing the primary key for a row after you inserted it will only give you problems. – Mark Byers Apr 06 '12 at 12:36
  • 1
    what if drop and recreate autoincreament field? –  Apr 06 '12 at 12:37
  • 2
    Dropping and recreating the column (or trying to renumber existing rows) won't work, especially if you've already used the IDs as a foreign key somewhere else. (It will break connections to existing rows.) You really should listen to @MarkByers - "Changing the primary key for a row after you inserted it will only give you problems." Also see [this](http://stackoverflow.com/questions/2106237/is-there-any-harm-in-resetting-the-auto-increment) SO question and it's answers. – Ken White Apr 06 '12 at 12:42
  • 2
    Why do (you think) you need this? – ypercubeᵀᴹ Apr 06 '12 at 12:42
  • Logically you never need this – Shakti Singh Apr 06 '12 at 12:47
  • I have two tables each for one language. And there were some changes in one table so I have make the same changes on the other table - manually. I don't use them as order, but the ids for the one and the other language should stay the same ... – testing Apr 06 '12 at 12:56

4 Answers4

14

I see no good reason for this. Only problems. Before running the folowing statement, check if you have FOREIGN keys defined, that reference this id. Are they set to ON UPDATE CASCADE? Also, do you have any triggers that are related to this table?

But first consider, why you (think you) need this. Is it going to be used for ordering the table? In that case, as @Mark pointed, you should use a separate column to specify your desired order.

If, however, you decide you really want this, use:

UPDATE myTable 
SET id = id + 1 
WHERE id >= 53
ORDER BY id DESC  ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • I don't use it for ordering. Is there a quick ways to check on foreign keys with MySQL Query Browser? The same for triggers ... – testing Apr 06 '12 at 13:11
  • To find if there are FKs **from** this table to other tables, just use `SHOW CREATE TABLE myTable`. To find if there are FKs **to** this table from others, you'll have to use the `information_schema` database. – ypercubeᵀᴹ Apr 06 '12 at 14:18
  • 1
    [Query to find foreign keys](http://stackoverflow.com/questions/7765820/query-to-find-foreign-keys) – ypercubeᵀᴹ Apr 06 '12 at 14:21
  • If I user `SELECT * FROM information_schema.TABLE_CONSTRAINTS T` I only see `PRIMARY KEY` and `UNIQUE`. So there should be no foreign keys. Is there also something for the triggers? – testing Apr 06 '12 at 14:43
  • 1
    `SHOW TRIGGERS` or `SHOW TRIGGERS FROM database_name`: [Show triggers syntax](http://dev.mysql.com/doc/refman/5.0/en/show-triggers.html) in MySQL. – ypercubeᵀᴹ Apr 06 '12 at 15:21
  • OK, don't know why I didn't come on the idea to use google for that. Thanks! Seems that there are no triggers. – testing Apr 06 '12 at 15:33
6

Quick and dirty you do it in 2 steps.

  • increase the id to a number higher that all others
  • decrease all ids to the number you want

Like that

UPDATE myTable SET id=id+10000 WHERE id >= 53
UPDATE myTable SET id=id-9999 WHERE id >= 53
juergen d
  • 201,996
  • 37
  • 293
  • 362
0

I guess something like that works :

UPDATE myTable SET id=id+1 WHERE id >= (select id from myTable order by id DESC)
Romain Hoog
  • 5
  • 2
  • @Ken: Despite being named `auto_...`, you can assign a value if you don't want the default auto_incremented one. – ypercubeᵀᴹ Apr 06 '12 at 12:44
  • @ypercube: Yuk. And thanks for the info :). Just one more reason not to use MySQL (for me, anyway). Deleting my comment above. – Ken White Apr 06 '12 at 13:42
  • AFAIK, the same applies to Identity columns in SQL-Server. But the usual behaviour is off course not to enter values manually. – ypercubeᵀᴹ Apr 06 '12 at 14:15
  • @ypercube, not without disabling the constraint first. A normal INSERT/UPDATE will ignore any value you try to assign to an identity column. That's why I said yuk about MySQL - being able to update an autoincrement column means it's not an autoincrement column. – Ken White Apr 09 '12 at 16:16
  • @KenWhite: You are right about the SQL-Server. SQL-Server Identity works differently than (MySQL) auto_increment. The UPDATE statement in MySQL has an even uglier "yuk" (which is the reason for the question actually). It works sequentially and not in a set operation. Yuk-yuk :) – ypercubeᵀᴹ Apr 09 '12 at 16:22
0

I had faced same problem. And also tried with query by OP and @Romain Hoog . But not succeeded.

Finally exported data of whole table in excel and done it in excel (not one by one but using trick that makes it very fast).

then taken backup of original table recreated new table and imported data from updated excel .

  • What you have done in Excel? Simply + 1 as formula? I thought about a CSV export and then editing the data here. Later with `LOAD DATA INFILE "data.csv" REPLACE INTO TABLE myTable ...` I could import the data. How do you have it done? – testing Apr 06 '12 at 13:26
  • done with drag + sign. I have imported data with UI tool not with LOAD Data. But you can do with this also . –  Apr 06 '12 at 13:30