current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

Hey Dba Stack Exchange,

My question to you is with regards to mysql. Right now I have a medium to small sized csv with 25k lines I need imported into mysql for a client. Problem is, their is only 4411 unique key rows. Rest have the same primary key and thus are skipped. I cant replace into because all the columns are present in the csv. I need to do (in mongodb terms Upsert).

Basically my question is what is the best method to update the existing columns like a ON DUPLICATE KEY during an insert for bulk csv file loading.

Database is Mysql

Table1 {

userID VARCHAR(50) PK,

UnixTimestamp VARCHAR(100),

likesCars TINYINT(1),

likesTrucks TINYINT(1),

HasMorgage TINYINT(1),

... 1000 more

}

Data:

2323232,1111111,1,0,0,0,1...

2323232,2333222,0,0,0,1,0... (needs merge)

1121121,222222,0,1,1,0,0...

goes on and on haha.

Was thinking maybe since they are mainly tiny ints changing

Load Data InFile CSv... (userId, @unixTimestamp, @likesCars, @likesTrucks...)

SET unixTimestamp = @unixTimestamp

SET likesCars = @LikesCars OR LikesCars

SET likesTrucks = @LikesTrucks OR LikesTrucks

Am I on the right track here?

share|improve this question
    
Please show the table structure of the table you want to load. Please also show a sample of row/columns that will be inserted into the table. –  RolandoMySQLDBA Jun 18 '13 at 16:16
    
Hopefully this helps –  Jake Steele Jun 18 '13 at 16:26
    
Question to help me understand: Are you saying the merge of the first lines should look like this: 2323232,2333222,1,0,0,1,1... ? –  RolandoMySQLDBA Jun 18 '13 at 16:44
    
Another Question: Does Table1 already have data in it ? –  RolandoMySQLDBA Jun 18 '13 at 17:00
    
Table 1 is empty, The first column is the ID, the second is the timestamp. The timestamp can just get replaced with the one farther down (lower in the file the later the timestamp would be generated). Also the above merge you posted would be correct. –  Jake Steele Jun 18 '13 at 17:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.