title: Importing (semi)-large CSV and Duplicate Data
body: <p>I'm using MySQL's <code>LOAD DATA INFILE</code> to import a CSV of about 150k rows nightly on a cronjob.</p>
  
  <p>The table definition is simple (CSV contains all columns except for ID):</p>
  
  <pre><code>CREATE TABLE products (
      id int(10) unsigned auto_increment
      category varchar(30) NOT NULL,
      subtype varchar(30) NOT NULL,
      manufacturer varchar(50) NOT NULL,
      part varchar(100) NOT NULL,
      PRIMARY KEY (id),
      UNIQUE KEY mfg_part (manufacturer,part)
  );
  </code></pre>
  
  <p><strong>Problem:</strong><br>
  I'm not sure how to handle deleting records that are no longer in the CSV.</p>
  
  <p><strong>My attempt:</strong><br>
  My idea was to add a batch number to the table and have LOAD DATA INFILE update each existing record with the latest batch number. A delete query would then remove all records whose batch number is not the latest. The load data query used for this is:</p>
  
  <pre><code>LOAD DATA INFILE '/path/to/products.csv'
  REPLACE INTO TABLE `products`
  FIELDS TERMINATED BY ','
  ENCLOSED BY '\"'
  LINES TERMINATED BY '\n'
  (`category`, `subtype`, `manufacturer`, `part`)
  SET `batch` = $newBatchNumber
  </code></pre>
  
  <p>Doesn't work though because <code>SET batch</code> causes MySQL to see each row as a whole new record, rather than updating the old batch number to the new.</p>
  
  <p><em>Is there a way around this?</em></p>
  
  <p>As it stands, the only other alternative I can think of is to switch from LOAD DATA INFILE to batches of INSERT ... ON DUPLICATE KEY UPDATE queries.</p>