title: Join and overwrite data in one table with data from another table
body: <p>How to join and overwrite data appears to be a common request, but I have yet to find an elegant solution that applies to an entire dataset.</p>
  
  <p>(Note: to simplify the data, I will use only 1s and NAs for values and a small subset of columns, but in reality I have hundreds of columns with different values).</p>
  
  <p>I have one data table (d1) that has NA values in certain columns and rows. </p>
  
  <pre><code>library(data.table)
  d1 = fread(
  "r id v1 v2 v3
  1  A  1  1  1
  2  B  1  1  1
  3  C  1 NA NA
  4  D  1  1 NA
  5  E  1 NA  1")[, r := NULL]
  </code></pre>
  
  <p>And I have another data table (d2) that consists of additional columns as well as data points missing from existing columns in d1. </p>
  
  <pre><code>d2 = fread(
  "r id v2 v3 v4 v5
  1  C  1  1  1  1
  2  D  1  1  1  1
  3  E  1  1  1  1")[, r := NULL ]
  </code></pre>
  
  <p>I would like to basically join + overwrite d1 with all the data in d2, making sure of course to match rows by id and columns by name, as shown below.  </p>
  
  <pre><code>&gt; d12
    id v1 v2 v3 v4 v5
  1  A  1  1  1 NA NA
  2  B  1  1  1 NA NA
  3  C  1  1  1  1  1
  4  D  1  1  1  1  1
  5  E  1  1  1  1  1
  </code></pre>
  
  <p><strong>Additional scenario:</strong> I'd also like to know how this can be done if you only want to update the NA values in d1, that is, make sure existing non-NA values are not overwritten. (To make this easier to visualize, I'm including new tables with both 1s and 0s).</p>
  
  <p>For example, if we have d3</p>
  
  <pre><code>d3 = fread(
  "r id v1 v2 v3
  1  A  1  1  1
  2  B  1  1  1
  3  C  1  0 NA
  4  D  1  1  0
  5  E  1 NA  1")[, r := NULL ]
  </code></pre>
  
  <p>And we want to join d2 and overwrite only NAs to get:</p>
  
  <pre><code>&gt; d32
    id v1 v2 v3 v4 v5
  1  A  1  1  1 NA NA
  2  B  1  1  1 NA NA
  3  C  1  0  1  1  1
  4  D  1  1  0  1  1
  5  E  1  1  1  1  1
  </code></pre>
  
  <p>FYI, below are some other posts addressing this problem but only for one or two columns. The solution I'm looking for should allow the data in one table to be overwritten by many if not all of the columns in another table.</p>
  
  <p><a href="https://stackoverflow.com/questions/16042380/merge-data-frames-and-overwrite-values">Merge data frames and overwrite values</a></p>
  
  <p><a href="https://stackoverflow.com/questions/33954292/merge-two-data-frame-and-replace-the-na-value-in-r">Merge two data frame and replace the NA value in R</a></p>
  
  <p>A <strong>data.table</strong>-based solution would be preferred, but others are welcome. </p>