CNB CNB - 2 months ago 7
MySQL Question

Insert non-repeating data into table

I've got a file that I've succesfully updated to a table. Now, I'm required to do it again in a way that the data is not being repeated.

my target table:

mysql> show columns from MARCAS;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| ID_MARCA | int(11) | NO | PRI | NULL | auto_increment |
| MARCA | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+


So what I did was to create a temporary table and loaded data from the file into it.

code used:

CREATE TEMPORARY TABLE tempe LIKE marcas;
LOAD DATA LOCAL INFILE myfile.txt
INTO TABLE MARCAS
FIELDS TERMINATED BY '#' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ID_MARCA, MARCA, @ignore1, @ignore2, @ignore3);


All fine here. The problem starts when I am trying to be smart and use 'ON DUPLICATE KEY'(like it was sugested here) to insert data from temporary table into table MARCAS as so data does not repeat itself. The code I used was:

INSERT into MARCAS select * from TEMPE on duplicate key update MARCA=values(MARCA);


The result:

mysql> select * from MARCAS;
+----------+---------+
| ID_MARCA | MARCA |
+----------+---------+
| 1 | PICASSA |
| 2 | PICASSA |
| 3 | C4 |
| 4 | C4 |
| 5 | C3 |
| 6 | C3 |
| 7 | C2 |
| 8 | C2 |
| 9 | MONDEO |
| 10 | MONDEO |
| 11 | S-MAX |
| 12 | S-MAX |
| 13 | CIVIC |
| 14 | CIVIC |
| 15 | ACCORD |
| 16 | ACCORD |
| 17 | CLS |
| 18 | 900 |
| 19 | LEON |
| 20 | LEON |
| 21 | IBIZA |
| 22 | IBIZA |
| 23 | 307 |
| 24 | 307 |
| 25 | 308 |
| 26 | 308 |
| 27 | 407 |
| 28 | 407 |
| 29 | 408 |
| 30 | 408 |
| 31 | MEGANE |
| 32 | MEGANE |
| 33 | PASSAT |
| 34 | PASSAT |
| 35 | GOLF |
| 36 | GOLF |
| 37 | TOUAREG |
+----------+---------+


Not impressed with myself. What am I doing wrong?

Answer

It's caused by the fact that you don't have a UNIQUE index on your MARCA column.

alter table MARCAS add unique(MARCA);

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

source: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Please also note that it's also possible to do INSERT IGNORE or A REPLACE when you do a load data more information here: Load data with duplicates

Comments