CODEWITHSUNDEEP
mysqlsqlinserton-duplicate-key
CNB
CNB

Reputation: 167

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?

Upvotes: 1

Views: 867

Answers (2)

e4c5
e4c5

Reputation: 53734

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.

Upvotes: 1

Akash Ganatra
Akash Ganatra

Reputation: 44

May you get some idea from this

INSERT INTO MARCAS(field1)
WHERE NOT EXISTS (
    SELECT field1 FROM MARCAS WHERE name = 'field1'
) LIMIT 1;

Upvotes: 0

Related Questions