title: Merge based on 2 columns values - Check for duplicate key values error
body: <p>I have two data tables with the following columns - <code>ddate,fnumber,file,model</code> and <code>fnumber,ddate,model,model_id,file</code>. I would like to update the first table with values from the second table matched by <code>ddate</code> and <code>fnumber</code> columns.</p>
  
  <p>If I use <code>merge</code>:</p>
  
  <pre><code>dtPT &lt;- merge(dtPT, dtAT, by = c("fnumber", "ddate"), all.x = TRUE)
  </code></pre>
  
  <p>then I get the following error - </p>
  
  <blockquote>
    <p>Error in vecseq(f__, len__, if (allow.cartesian || notjoin) NULL else
    as.integer(max(nrow(x),  :    Join results in 8568291 rows; more than
    8537179 = max(nrow(x),nrow(i)). Check for duplicate key values in i,
    each of which join to the same group in x over and over again. If
    that's ok, try including <code>j</code> and dropping <code>by</code> (by-without-by) so that
    j runs for each group to avoid the large allocation. If you are sure
    you wish to proceed, rerun with allow.cartesian=TRUE. Otherwise,
    please search for this error message in the FAQ, Wiki, Stack Overflow
    and datatable-help for advice.</p>
  </blockquote>
  
  <p>I tried to search for duplicated records in <code>dtAT</code>:</p>
  
  <pre><code>setkeyv(dtAT, c("fnumber", "ddate"))
  dtAT[duplicated(dtAT)]
  </code></pre>
  
  <p>but it returns 0 rows.</p>
  
  <p>I also tried to use <code>match</code> instead, but it doesn't work as I expected (sample data is used below):</p>
  
  <pre><code>tPT &lt;- "ddate,fnumber,file,model
  2014-05-26,S71149,ps1.csv,320
  2014-09-26,,ps2.csv,
  2014-10-16,,ps3.csv," 
  
  tAT &lt;- 
  "fnumber,ddate,model,model_id,file
  S71149,2014-05-26,319,VU-BHP,as1.csv
  S71149,2014-05-25,320,,as2.csv
  S71149,2014-05-23,322,VU-BTP,as3.csv"
  
  columnClasses &lt;- c("POSIXct", "factor", "character", "factor")
  dtPT &lt;- read.csv(text=tPT, header = TRUE, sep = ",", na.strings = c(""), colClasses = columnClasses)
  dtPT &lt;- as.data.table(dtPT)
  
  columnClasses &lt;- c("character", "POSIXct", "character", "factor", "factor")
  dtAT &lt;- read.csv(text=tAT, header = TRUE, sep = ",", na.strings = c(""), colClasses = columnClasses)
  dtAT &lt;- as.data.table(dtAT)
  
  dtPT$model_code &lt;- dtAT[match(paste(dtAT$fnumber, dtAT$ddate), paste(dtPT$fnumber, dtPT$ddate)), dtAT$model] 
  </code></pre>
  
  <p>The code above in result assigns <code>model_code</code> values to all rows:</p>
  
  <pre><code>        ddate fnumber    file model model_code
  1: 2014-05-26  S71149 ps1.csv   320        319
  2: 2014-09-26      NA ps2.csv    NA        320
  3: 2014-10-16      NA ps3.csv    NA        322
  </code></pre>
  
  <p>when <code>320</code> should assigned to the first row only:</p>
  
  <pre><code>        ddate fnumber    file model model_code
  1: 2014-05-26  S71149 ps1.csv   320        319
  2: 2014-09-26      NA ps2.csv    NA         NA
  3: 2014-10-16      NA ps3.csv    NA         NA
  </code></pre>
  
  <p>Since for second and third rows there is no match by combination of <code>ddate</code> and <code>fnumber</code>.</p>
  
  <p>(the <code>merge</code>, of course, works well with the example below, but returns the error with Production data)</p>