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
– BugFinder, Commented Oct 15, 2012 at 7: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
– Hasitha Shan, Commented Oct 15, 2012 at 7:59

Omar · Accepted Answer · 2012-11-16 23:03:19Z

70

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, 2012 at 23:03

Omar

7165 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mike Mackintosh Over a year ago

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

shabbychef Over a year ago

I believe you mean UNIQUE index on the name column.

user6079755 Over a year ago

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.

david strachan · Accepted Answer · 2012-10-15 09:52:07Z

6

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, 2012 at 9:52

david strachan

7,2282 gold badges25 silver badges33 bronze badges

3 Comments

Hasitha Shan Over a year ago

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

Hasitha Shan Over a year ago

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

Ataboy Josef Over a year ago

Check the correct answer for the question stackoverflow.com/questions/15271202/…

RobinKay · Accepted Answer · 2018-05-30 08:03:30Z

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, 2018 at 8:03

RobinKay

114 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related