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 :(

Mezcaline

You can only have 1 member with a given age? Make age unique. – Attainture

nope not like that, i meant a member in the table can also be available in the csv file, i want to ignore those members and upload the rest :) – Mezcaline

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);

Astroid

the IGNORE keyword is just what most people are looking for. great job. – Locris

I believe you mean UNIQUE index on the name column. – Fixture

Hello friends, I have a table with 7 columns, the primary key does not work for me very much for this query, since it is based on columns and values, I want to insert or update a single column from a csv file; The problem is that Duplicate is not used correctly, and if possible for this scenario: if in a row three of the columns A, B, C match their values (already exists a record) do the update; If there is no match make an insert in the queue. – Garbo

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.)
            )

Rendarender

okeyy..il will try that out and post back :) thank you very much – Mezcaline

thanx man..just what i needed :) but if there is any direct way of doing this, please let me know :) thanx again :) – Mezcaline

Check the correct answer for the question #15271702 – Drier

Answer 3

You can create a unique index on multiple columns. LOAD DATA won't insert rows that match existing rows on all of those columns.

e.g. ALTER TABLE tbl_member ADD UNIQUE unique_index(name,age)

Germinant