Re: moving data to MySQL
2009/2/21 Chris Rehm ch...@javadisciple.com I want to write some programs to work with data from eveonline.com but my installed database is MySQL and the data format they provide is a backup of MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded that and am willing to install it, I just want to know if there is a programmatic way of transferring the data to MySQL. Any help or insight would be appreciated, I have been away from coding for several years because of health issues and I'm trying to knock the rust off my brain and get going again. Chris Rehm ch...@javadisciple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dimitar.vassi...@gmail.com You can make a ODBC/JDBC connection, export to CSV file, create a db link also
Re: help on creating missing ids
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 Drtnull 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
help on creating missing ids
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. -- Димитър Василев Dimitar Vassilev GnuPG key ID: 0x4B8DB525 Keyserver: pgp.mit.edu Key fingerprint: D88A 3B92 DED5 917E 341E D62F 8C51 5FC4 4B8D B525
Re: showing duplicate text
2007/2/10, Dimitar Vasilev [EMAIL PROTECTED]: Hi all, First I would like to thank you for the invaluable pieces of advice. I've made a good progress thanks to you. Now I'm trying to display the output of people who have shareholders at more than one place select COUNT (*),IME,LAND,CNT,ID, (select round(bulstat,0)) from part GROUP BY IME,LAND,CNT,ID HAVING COUNT(*)'1' AND CNT '0'. Currently it gives me the number of times a person is a shareholder If I make it like: select IME,LAND,Company,CNT,(select round(bulstat,0)) from part WHERE IME LIKE 'Pooh' AND CNT '0'; output is like this Pooh, Forest, Big Honeypot, 50 Can someone shed some light how to make it like the second one for all people. I can make a dump to a text file of the names I want, then assign a variable to the names list and then use select IME,LAND,Company,CNT,(select round(bulstat,0)) from part WHERE IME LIKE '@ownerlist' AND CNT '0'; but I am not sure that text arrays (1 row per line ) will work. Thank you and happy week-end. -- Димитър Василев Dimitar Vassilev GnuPG key ID: 0x4B8DB525 Keyserver: pgp.mit.edu Key fingerprint: D88A 3B92 DED5 917E 341E D62F 8C51 5FC4 4B8D B525 select *,(select round(bulstat,0)) from part GROUP BY IME,LAND,CNT,bulstat HAVING COUNT(*) '1' and CNT '0'; Thanks. -- Димитър Василев Dimitar Vassilev GnuPG key ID: 0x4B8DB525 Keyserver: pgp.mit.edu Key fingerprint: D88A 3B92 DED5 917E 341E D62F 8C51 5FC4 4B8D B525
showing duplicate text
Hi all, First I would like to thank you for the invaluable pieces of advice. I've made a good progress thanks to you. Now I'm trying to display the output of people who have shareholders at more than one place select COUNT (*),IME,LAND,CNT,ID, (select round(bulstat,0)) from part GROUP BY IME,LAND,CNT,ID HAVING COUNT(*)'1' AND CNT '0'. Currently it gives me the number of times a person is a shareholder If I make it like: select IME,LAND,Company,CNT,(select round(bulstat,0)) from part WHERE IME LIKE 'Pooh' AND CNT '0'; output is like this Pooh, Forest, Big Honeypot, 50 Can someone shed some light how to make it like the second one for all people. I can make a dump to a text file of the names I want, then assign a variable to the names list and then use select IME,LAND,Company,CNT,(select round(bulstat,0)) from part WHERE IME LIKE '@ownerlist' AND CNT '0'; but I am not sure that text arrays (1 row per line ) will work. Thank you and happy week-end. -- Димитър Василев Dimitar Vassilev GnuPG key ID: 0x4B8DB525 Keyserver: pgp.mit.edu Key fingerprint: D88A 3B92 DED5 917E 341E D62F 8C51 5FC4 4B8D B525
rounding digits after decimal sign
Hello, Can anyone point me to a section of manual or link how to reduce digits after a decimal sign? I have a table mysql desc part; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | kod | varchar(255) | YES | | NULL| | | bulstat | double(15,5) | YES | MUL | NULL| | | Land| varchar(3) | YES | MUL | NULL| | | ID | varchar(15) | YES | MUL | NULL| | | IME | varchar(100) | YES | MUL | NULL| | | VALUE | double(15,5) | YES | | NULL| | | CNT | smallint(5) | YES | | NULL| | | Date| datetime | YES | | NULL| | +-+--+--+-+-+---+ and in bulstat column i have digits like 831690750.0 which i would like round to the last whole digit. Also could someone give a hint how to recode digits in order to anonymize data. My end goal is to export the dataset into a social network software for my thesis. Thanks, -- Димитър Василев Dimitar Vassilev GnuPG key ID: 0x4B8DB525 Keyserver: pgp.mit.edu Key fingerprint: D88A 3B92 DED5 917E 341E D62F 8C51 5FC4 4B8D B525
