[ale] Renumber keys in mysql

[Omar Chanouha]

If that works, then why create temp at all? Just nest the select in the line:

insert into original (name,...) select name,... from (select * from
original where id > 50 and id < 150);

http://dev.mysql.com/doc/refman/5.5/en/insert-select.html

That page may help. I think comment one is along the lines of your situation.

-O

On Thu, Jul 21, 2011 at 11:41 AM, David Tomaschik
<david at systemoverlord.com> wrote:
> 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
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://mail.ale.org/mailman/listinfo/ale
> See JOBS, ANNOUNCE and SCHOOLS lists at
> http://mail.ale.org/mailman/listinfo
>