insert data and avoid duplication by checking a specific column

Question

I have a local db that I'm trying to insert multiple rows of data, but I do not want duplicates. I do not have a second db that I'm trying to insert from. I have an sql file. The structure is this for the db I'm inserting into:

(db)artists
    (table)names->     ID  |  ArtistName  |  ArtistURL  |  Modified

I am trying to do this insertion:

INSERT names (ArtistName, Modified) 
VALUES (name1, date),
       (name2, date2),
       ...
       (name40, date40)

The question is, how can I insert data and avoid duplication by checking a specific column to this list of data that I want inserted using SQL?

How are you feeding the values in from the CSV? Do you have a script to format into (name1,date) format? — EoinS, Sep 27 '16 at 17:43
Yes, all values are in sql format. I use 'import' with an sql file from phpmyadmin. I've gotten it to insert all my data correctly, but I had a bunch of duplicates so I had to rollback — DJSweetness, Sep 27 '16 at 17:46
You can use a `LEFT JOIN ... WHERE b.ArtistName IS NULL` to only insert new records. If you can't join between the two db then stage the records into a separate table first, then do the insert from there. — Hart CO, Sep 27 '16 at 17:51

score 0 · Answer 1 · answered Sep 28 '16 at 21:32

0

Duplicate what? Duplicate name? Duplicate row? I'll assume no dup ArtistName.

Have UNIQUE(ArtistName) (or PRIMARY KEY) on the table.
Use INSERT IGNORE instead of IGNORE.

(No LEFT JOIN, etc)

answered Sep 28 '16 at 21:32

Rick James

135,179
13
127
222

score 0 · Answer 2 · answered Oct 10 '16 at 16:33

I ended up following the advice of @Hart CO a little bit by inserting all my values into a completely new table. Then I used this SQL statement:

SELECT ArtistName
FROM testing_table
WHERE !EXISTS 
(SELECT ArtistName FROM names WHERE 
testing_table.ArtistName = testing_table.ArtistName)

This gave me all my artist names that were in my data and not in the name table. I then exported to an sql file and adjusted the INSERT a little bit to insert into the names table with the corresponding data.

INSERT IGNORE INTO `names` (ArtistName) VALUES
*all my values from the exported data*

Where (ArtistName) could have any of the data returned. For example, (ArtistName, ArtistUrl, Modified). As long as the values returned from the export has 3 values.

This is probably not the most efficient, but it worked for what I was trying to do.

insert data and avoid duplication by checking a specific column

2 Answers2