MYSQL LOAD DATA INFILE ignore duplicate rows (autoincrement as primary key)

Question

I ran into some trouble using LOAD DATA INFILE command as i wanted to ignore the lines that was already in the data base..say if i have a table with data as follows,

id  |name   |age -------------------- 1   |aaaa   |22 2   |bbbb   |21 3   |bbaa   |20 4   |abbb   |22 5   |aacc   |22 

Where id is auto increment value. an the csv file i have contains data as follows,

"cccc","14" "ssee","33" "dddd","22" "aaaa","22" "abbb","22" "dhgg","34" "aacc","22" 

I want to ignore the rows,

    "aaaa","22"     "abbb","22"     "aacc","22" 

and upload the rest to the table. and the query i have yet which uploads everything to the table is as follows,

    LOAD DATA INFILE 'member.csv'     INTO TABLE tbl_member     FIELDS TERMINATED BY ','            ENCLOSED BY '"'            ESCAPED BY '"'            LINES TERMINATED BY '\n'     (name, age); 

PLEASE help me on this task.. It will be much appreciated..i tried many links but did not help :(

Answer 1

Create a UNIQUE index on the age column, then:

LOAD DATA INFILE 'member.csv' IGNORE INTO TABLE tbl_member FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\n' (name, age); 

Answer 2

One approach is to use a temporary table. Upload to this and use SQL to update tbl_member from temp table.

INSERT INTO tbl_member SELECT Field1,Field2,Field3,...  FROM temp_table WHERE NOT EXISTS(SELECT *               FROM tbl_member               WHERE (temp_table.Field1=tbl_member.Field1 and                    temp_table.Field2=tbl_member.Field2...etc.)             )