CODEWITHSUNDEEP
mysqlauto-increment
LGB
LGB

Reputation: 718

How can I update an existing record to have a new auto_increment id in MySQL?

I have a table with primary key (its name is "id") defined as auto_increment. I use NULL in INSERT statements to "fill" the id value. It works, of course. However now I need to "move" an existing record to a new primary key value (the next available, the value is not so much important, but it must be a new one, and the last one if ordered by id). How can I do it in an "elegant" way? Since the "use NULL at INSERT" does not work too much with UPDATE:

update idtest set id=NULL where id=1;

This simply makes the id of the record zero. I would expect to do the same thing as with INSERT, but it seems my idea was incorrect.

Of course I can use "INSERT ... SELECT" statement, then a DELETE on the old one, or I can use something like MAX(id) + 1 to UPDATE the id of the old record in one step, etc, but I am curious if there is a finer solution.

Also, the MAX(id) solution doesn't seem to work either by the way:

mysql> update idtest set id=max(id)+1 where id=3;
ERROR 1111 (HY000): Invalid use of group function
mysql> update idtest set id=(select max(id)+1 from idtest) where id=3;
ERROR 1093 (HY000): You can't specify target table 'idtest' for update in FROM clause

Upvotes: 1

Views: 12069

Answers (3)

user2009750
user2009750

Reputation: 3197

This is the way I believe:

UPDATE users SET id = (SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'test'
AND   TABLE_NAME   = 'users') WHERE id = 2;

select * from users;

I used by own tables substitute yours.

test is database name, users is table name and id is AUTO_INCREMENT in my case.

EDIT: My Query above works perfect but its side effects are somewhat 'dangerous', upon next insert as AUTO_INCREMENT value will collide with this recently updated record so just next single insert will fail. To avoid that case I've modified above query to a transaction:

START transaction;

    UPDATE users SET id = (SELECT `AUTO_INCREMENT`
        FROM  INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = 'test'
        AND   TABLE_NAME   = 'users') WHERE id = 2;

    #renew auto increment to avoid duplicate warning on next insert
    INSERT IGNORE INTO users(username) values ('');

COMMIT

Hope this will help someone if not OP.

Upvotes: 3

Pred
Pred

Reputation: 9042

You can use the REPLACE INTO clause to do the trick.

From the manual:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.5, "INSERT Syntax".

EDIT My mistake (in the comments) that you have to have two unique constraint to achieve this:

When you use the auto_increment value to REPLACE the record, the record will be replaced with the give ID and will not change (however the AI value will increment).

You have to exclude the AI column from the query. You can do that if you have one more UQ constraint.

Check this SQLFiddle demo: http://sqlfiddle.com/#!2/1a702e

The first query will replace all the records (but the id's value will not change). The second one will replace it too, and the new AI value will be used. (Please note, that the second query does not contain the id column, and there is a UQ constraint on the some column).

You can notice, that the second query uses higher AI values than it is excepted: this is because the first replace incremented the AI value.

If you do not have two unique keys (one for the AI and one for another columns), the REPLACE statement will work as a normal INSERT statement!

(Ofcourse you can change one of the UNIQUE KEYs with a PRIMARY KEY)

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

The way you are trying to update same table is wrong but you can use join on same table

update idtest t
join (select id +1 as id
    from idtest order by id desc 
    limit 1) t1
set t.id=t1.id
where t.id=3;

or 

update idtest t
join (select max(id) +1 as id
    from idtest ) t1
set t.id=t1.id
where t.id=3;

Upvotes: 2

Related Questions