mysql question
This has been bothering me for a while. I found a work-around the first time I ran into it, but a work-around isn't as obvious this time around...
Is there any way to load data from multiple files into the same table without each successive file stomping on all that went before? I want to create a table with a primary key column (gene_name) and a bunch of data columns (condition_1, condition_2, etc). The data for each condition is in a separate file, indexed to the same primary key as the table. I want to sequentially load each file into the table, so that condition_1_file data goes into condition_1_column, and then condition_2_file goes into condition_2_column, and so on, with each data point matched to the correct row (gene_name).
Seems easy, but when I try to do a "load data ... replace into ..." command, the second file that loads wipes out all the entires from the first file in the same row. After poring over the MySQL manual, there seems to be no way to just replace certain columns of a row, instead of the entire row. At least from a file - the "Update" command seems to do exactly what I want, but you have to manually enter the new values. Not an option for twenty thousand data points.
Any suggestions? My life will be a lot easier if I can figure out an easy way to do this.
(I'm using MySQL 3.23.53 on OSX10.2.4; as far as I can tell from the version 4 manual, upgrading won't help - but I'll do it if it will)
Is there any way to load data from multiple files into the same table without each successive file stomping on all that went before? I want to create a table with a primary key column (gene_name) and a bunch of data columns (condition_1, condition_2, etc). The data for each condition is in a separate file, indexed to the same primary key as the table. I want to sequentially load each file into the table, so that condition_1_file data goes into condition_1_column, and then condition_2_file goes into condition_2_column, and so on, with each data point matched to the correct row (gene_name).
Seems easy, but when I try to do a "load data ... replace into ..." command, the second file that loads wipes out all the entires from the first file in the same row. After poring over the MySQL manual, there seems to be no way to just replace certain columns of a row, instead of the entire row. At least from a file - the "Update" command seems to do exactly what I want, but you have to manually enter the new values. Not an option for twenty thousand data points.
Any suggestions? My life will be a lot easier if I can figure out an easy way to do this.
(I'm using MySQL 3.23.53 on OSX10.2.4; as far as I can tell from the version 4 manual, upgrading won't help - but I'll do it if it will)
Comments
Load each file into its own table. Then create the desired table with something akin to:
create table gene_table as (
select t1.gene_name,
t2.condition_1,
t3.condition_2,
....
from gene_name_table t1,
cond_1_table t2,
cond_2_table t3,
....
where t1.rowid = t2.rowid
and t2.rowid = t3.rowid
and t3.rowid = t4.rowid
.....
);
In oracle, the data loaded from the file using sqlldr is put into the tables sequentially and .rowid is a special attribute of the table giving the row id. If mysql doesn't have a similar capability, then when uploading the file I would hope you could also create an id field autopopulated with the row number. If the loading function of mysql doesn't support this, it isn't hard to add this to the files with a script or even simple C or java program.