[ale] Renumber keys in mysql

[david at systemoverlord.com (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