MySQL Duplicates with Load Data Infile

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

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

mysql duplicates with LOAD DATA INFILE

From the LOAD DATE INFILE documentation:

The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key values:

• If you specify REPLACE, input rows replace existing rows. In other words, rows that have the same value for a primary key or unique index as an existing row. See Section 12.2.7, “REPLACE Syntax”.
• If you specify IGNORE, input rows that duplicate an existing row on a unique key value are skipped. If you do not specify either option, the behavior depends on whether the LOCAL keyword is specified. Without LOCAL, an error occurs when a duplicate key value is found, and the rest of the text file is ignored. With LOCAL, the default behavior is the same as if IGNORE is specified; this is because the server has no way to stop transmission of the file in the middle of the operation.

Effectively, there's no way to redirect the duplicate records to a different table. You'd have to load them all in, and then create another table to hold the non-duplicated records.

MySQL LOAD DATA LOCAL INFILE avoiding duplicates

I don't know how you got tablePKID to auto increment from 0 putting that to one side for now you can specify a unique key on samplid and testid and load..IGNORE for example

DROP TABLE IF EXISTS T;
CREATE TABLE T
(tablePKID   INT auto_increment primary key, 
sampleID VARCHAR(10),   testID VARCHAR(10));

alter table t
    add unique key k1(sampleid,testid);

INSERT INTO T (sampleid,testid) VALUES
(   '0001'   ,     'A'),
(   '0001'   ,     'B'),
(   '0003'   ,     'A'),
(   '0003'   ,     'B');

File data.txt

sampleid    testid
"0001"  "A"
"0001"  "B"
"0001"  "C"
"0005"  "A"
"0005"  "B"

LOAD DATA LOCAL INFILE 'C:\\Program Files\\MariaDB 10.1\\data\\sandbox\\data.txt'
     ignore
    INTO TABLE t 
    FIELDS TERMINATED by '\t'
    enclosed by '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES 
    (sampleID, testID);

+-----------+----------+--------+
| tablePKID | sampleID | testID |
+-----------+----------+--------+
|         1 | 0001     | A      |
|         2 | 0001     | B      |
|         3 | 0003     | A      |
|         4 | 0003     | B      |
|         5 | 0001     | C      |
|         6 | 0005     | A      |
|         7 | 0005     | B      |
+-----------+----------+--------+
7 rows in set (0.00 sec)

MYSQL LOAD DATA INFILE ignore duplicate rows, with non-unique value columns

Create a composite uniqe index over the 2 fields fruit and colour.

ALTER TABLE fruit_table 
ADD UNIQUE KEY (`fruit` , `colour`) ;

---

Related Topics