[ale] Renumber keys in mysql

[Chris Fowler]

I'm trying to do something easier in MySQL than write a perl program to
do it.

I have a list of 1000 items and I need to take 100 of these items, make
a slight change, and them copy them into the list ending up with 1100
items.  The problem is that I'm using a primary key.

1.  Create temp table as a copy of other

create table temp like original.

2.  Now copy the items I want to change into temp

insert into temp select * from original where id > 50 and id < 150; 

Now I have those 100 items in the temp table;

3.  Update those

update temp sent column='XXXX';

4.  Now I have to copy them back into original but what about the keys?

I've tried to renumber the ids in the temp table to those that are not
used in original

mysql> alter table temp drop id, ADD id INT UNSIGNED NOT NULL
AUTO_INCREMENT FIRST, auto_increment=1147;

ERROR 1089 (HY000): Incorrect prefix key; the used key part isn't a
string, the used length is longer than the key part, or the storage
engine doesn't support unique prefix keys

Here is a decription of temp:

+--------+---------------------+------+-----+-------------------+-----------------------------+
| Field  | Type                | Null | Key | Default           | 
+--------+---------------------+------+-----+-------------------+-----------------------------+
| id     | int(11) unsigned    | NO   | PRI | NULL              |
auto_increment              |
| name   | varchar(64)         | NO   | UNI | NULL              


Can someone tell me what I'm doing wrong?

Chris