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
