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.
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ /
/ /|_/ / // /\ \/ /_/ / /__
/_/ /_/\_, /___/\___\_\___/
Join the Quality Contribution Program Today!