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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
