2007/9/22, Shawn Green <[EMAIL PROTECTED]>: > > Hello Dimitar, > > Dimitar Vasilev wrote: > > Hi all, > > I have a table shares that consists of > > company id, shareholder id, shareholder name. > > Company id and shareholder id are decimal digits; shareholder name is > text. > > Due to some missings of my initial data, I'd like to create a unique id > for > > the shareholder ids > > which are blank/null in the imported version and are the same for every > > shareholder that > > appears into couple of companies. > > So far I've got to: > > create table shares (company id not null, shareholder id not null, > > shareholder name not null) PRIMARY KEY ( shareholder name) > > > > Then I'd like to update the missing shareholder id with autoincrement > and > > group them by shareholder name, but I'm not sure how to do it. > > > > I'm thinking of something like update autoincrement id where id is null > > group by shareholder name. > > > > Any straws/pointers are welcome. > > > > Thanks in advance. > > > > Are you trying to say that you would like a unique ID for each > shareholder name? or only one ID for each name that appears within a > company? In the second question, if the same name is associated with two > or more companies, it would have a separate id for each. > > You can do the second scenario rather easily > > create table shares (company_id int not null > , shareholder_id int not null auto_increment > , shareholder_name varchar(75) not null > , PRIMARY KEY (company_id, shareholder_id) > , UNIQUE KEY (shareholder_name) > ); > > Then just insert data like > > INSERT IGNORE shares (company_id, shareholder_name) > VALUES (1, 'name1'), (1,'name2'), (2,'name1'), (1,'name1'); > > I repeated a 'name1' value for company 1 so that you can see how both > INSERT IGNORE works and the sub-assignment for the auto_increment value. > > Best wishes, > > -- > 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 >
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. -- Димитър Василев Dimitar Vassilev
