SQL - Replace or skip duplicate entries

Question

I'm using MySQLConnector for Python to record all of the filepaths in a certain file tree to a SQL table on a regular basis (like on a cronjob or such). What I'm trying to do is write to the table if the filepath is new to the table, else update the table if it finds a pre-existing file path, thus not having duplicate entries. I've experimented with INSERT IGNORE and ON DUPLICATE KEY UPDATE but don't understand how to use it to my needs, since it has been failing me. The bit of code in particular is:

insertion = ("INSERT IGNORE INTO storage_folders (folder_path, company) VALUES ('" + filepath + "', '" + company + "')")

So I want the table to possess unique folder_paths, with no duplicates. The "company" column does not have to be (and should not) be unique. Can anyone point me to the right path as to how to I do this?

If `folder_path` is a primary key (or has a unique constraint), MySQL supports `INSERT... ON DUPLICATE KEY UPDATE` commands. See [MYSQL INSERT or UPDATE IF](http://stackoverflow.com/questions/14259249/mysql-insert-or-update-if) — rutter, Sep 10 '13 at 00:08

score 0 · Accepted Answer · answered Sep 10 '13 at 02:26

I prefer the SET syntax rather than () VALUES (), and you just need to make sure you are updating the column that is not the primary key. So this assumes that folder_path is the primary key

"INSERT INTO storage_folders 
 SET folder_path='" + filepath + "', company='" + company + '"
 ON DUPLICATE KEY UPDATE company='" + company + "'"

SQL - Replace or skip duplicate entries

1 Answers1