From: Shawn Green
Date: September 23 2007 2:13am
Subject: Re: help on creating missing ids
List-Archive: http://lists.mysql.com/mysql/209218
Message-Id: <46F5CBD8.9070706@mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Dimitar Vasilev wrote:
> 
> Hi Shawn,
> Thanks for your response.
> I'm trying to assign uniquely a user id per share holder within the whole
> list, not within each company.
> Suppose we have a person John Smith that holds shares both in MonkeyBusiness
> and NoSuchThing.
> I'd like to give him an id that is the same for all his participations in
> the list of companies
> 
> A bit of data sample:
> 
> company id shareholder id  shareholder name
> AXy                  null                       John Smith
> XyZ                  null                       Tom Gray
> Drt                    null                      John Smith
> XyZ                   null                       Lady Anne
> FFF                  null                       Tom Gray
> FTY                  null                      Lady Anne
> 
> Apologies for not sending a data sample earlier - it was 2 am when I
> finished poking into migrating the data and next day had to be
> early at my uni.

That's not a problem.  What I worry about is if you have more than one 
John Smith (say one from London and one from Rome). You should not give 
them both the same shareholder_id. However, one way to assign unique 
identifiers to a list of names is to normalize the data.

CREATE TABLE shareholders (
   id int auto_increment
, name varchar(50)
, primary key (id)
, Unique (name)
)

Then use the INSERT IGNORE command to populate just the `name` field 
from your other data. What that will do for you is to assign a unique 
identifier to each of your names. Then you can use an UPDATE statement 
to modify your other data to reflect the newly generated shareholder id's.

Yours,
-- 
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
     __  ___     ___ ____  __
    /  |/  /_ __/ __/ __ \/ /
   / /|_/ / // /\ \/ /_/ / /__
  /_/  /_/\_, /___/\___\_\___/
         <___/
  Join the Quality Contribution Program Today!
  http://dev.mysql.com/qualitycontribution.html