[ale] Renumber keys in mysql

[David Tomaschik]

On Thu, Jul 21, 2011 at 11:23 AM, Chris Fowler
<cfowler at outpostsentinel.com> wrote:
> 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

Why not just select everything except id from temp back into original?

insert into original (name,...) select name,... from temp;

-- 
David Tomaschik, RHCE, LPIC-1
System Administrator/Open Source Advocate
OpenPGP: 0x5DEA789B
http://systemoverlord.com
david at systemoverlord.com