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
