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

You can only have 1 member with a given age? Make age unique. — BugFinder, Oct 15 '12 at 07:56
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 :) — Hasitha Shan, Oct 15 '12 at 07:59

score 57 · Accepted Answer · answered Nov 16 '12 at 23:03

57

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

answered Nov 16 '12 at 23:03

Omar

586
4
3

22

the `IGNORE` keyword is just what most people are looking for. great job. – Mike Mackintosh Dec 06 '13 at 13:33
2

I believe you mean UNIQUE index on the _name_ column. – shabbychef Dec 10 '15 at 00:33
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. – Nov 09 '16 at 21:50

score 5 · Answer 2 · answered Oct 15 '12 at 09:52

5

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

answered Oct 15 '12 at 09:52

david strachan

6,992
2
21
33

okeyy..il will try that out and post back :) thank you very much – Hasitha Shan Oct 15 '12 at 10:30
thanx man..just what i needed :) but if there is any direct way of doing this, please let me know :) thanx again :) – Hasitha Shan Oct 16 '12 at 06:00
Check the correct answer for the question http://stackoverflow.com/questions/15271202/mysql-load-data-infile-with-on-duplicate-key-update – Ataboy Josef Mar 20 '15 at 05:56

score 0 · Answer 3 · answered May 30 '18 at 08:03

0

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)

answered May 30 '18 at 08:03

RobinKay

3
3

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

3 Answers3

Linked