title: Assist with MySql Query - Update based on select between tables
body: <p>I've been hitting my head against the wall about this problem for the last 8 hours and I still can't find a solution.</p>
  <p>This was my first post on stackoverflow, so sorry if I'm committing any kind of mistake writing this post.</p>
  <p>Based on previous answers, I have to agree that CREAT VIEW might be a better and easier option than using UPDATE for my case.</p>
  <p>I'll try to explain my problem:</p>
  <p>Brief contextualization: I have a table with quality control results and another with the product specification. What I have to do, is to insert the specification in the results table row by row in order to make it easier for me to statistically treat a comparison between results and specs and plot charts. The main problem I encountered is that I have different specifications for the same product (they change over time) and because of that I have to insert different specifications in the result's table based on the result's date.
  For Example: Let's say I have two Specs date: 2020-01-01 and 2020-05-01
  If I have a result on 2020-01-03 I have to insert in this result row the spec of the 2020-01-01. However, if the result date is 2020-05-02 I have to insert the spec from 2020-05-01. (always the newest specification which is older than the result date)
  I couldn't simply compare the two dates using WHERE results date &gt; spec date because both spec's dates will be older then the result's date for the 2020-05-02.</p>
  <p>Visual Example:
  MySQL version 8.0.23. (It supports PARTITION OVER and WITH)</p>
  <p>TABLE &quot;TABLE_A&quot;: Containing the results of the quality control</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Date</th>
  <th>Result</th>
  <th>Product</th>
  <th>Spec</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2020-01-01</td>
  <td>24,5</td>
  <td>ProductA</td>
  <td></td>
  </tr>
  <tr>
  <td>2</td>
  <td>2020-01-02</td>
  <td>25,5</td>
  <td>ProductA</td>
  <td></td>
  </tr>
  <tr>
  <td>3</td>
  <td>2020-01-03</td>
  <td>31,4</td>
  <td>ProductA</td>
  <td></td>
  </tr>
  <tr>
  <td>4</td>
  <td>2020-01-01</td>
  <td>24.3</td>
  <td>ProductB</td>
  <td></td>
  </tr>
  <tr>
  <td>5</td>
  <td>2020-01-02</td>
  <td>30.4</td>
  <td>ProductB</td>
  <td></td>
  </tr>
  <tr>
  <td>6</td>
  <td>2020-01-03</td>
  <td>41.4</td>
  <td>ProductB</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Data Type:</strong> ID PrimaryKey Auto increment /
  Date as Date() /
  Results, Product and Specification as Varchar()</p>
  <p>TABLE &quot;TABLE_B&quot;: containing the required specification for each product - It can contain multiple rows for the same product since the specification can change over time.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Date</th>
  <th>Spec</th>
  <th>Product</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2018-01-01</td>
  <td>20.0</td>
  <td>ProductA</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2020-01-02</td>
  <td>30.0</td>
  <td>ProductA</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2018-01-01</td>
  <td>24.3</td>
  <td>ProductA</td>
  </tr>
  <tr>
  <td>4</td>
  <td>2020-01-03</td>
  <td>30.4</td>
  <td>ProductA</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>Data Type:</strong> ID PrimaryKey Auto increment /
  Date as Date() /
  Product and Specification as Varchar()</p>
  <p>THE EXPECTED OUTCOME WOULD BE:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ID</th>
  <th>Date</th>
  <th>Result</th>
  <th>Product</th>
  <th>Spec</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>2020-01-01</td>
  <td>24,5</td>
  <td>ProductA</td>
  <td>20.0</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2020-01-02</td>
  <td>25,5</td>
  <td>ProductA</td>
  <td>30.0</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2020-01-03</td>
  <td>31,4</td>
  <td>ProductA</td>
  <td>30.0</td>
  </tr>
  <tr>
  <td>4</td>
  <td>2020-01-01</td>
  <td>24.3</td>
  <td>ProductB</td>
  <td>24.3</td>
  </tr>
  <tr>
  <td>5</td>
  <td>2020-01-02</td>
  <td>30.4</td>
  <td>ProductB</td>
  <td>24.3</td>
  </tr>
  <tr>
  <td>6</td>
  <td>2020-01-03</td>
  <td>41.4</td>
  <td>ProductB</td>
  <td>30.4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I could come up with was the SELECT part, but it is only returning the Specs for the product that appears on both tables. I also needed to return NULL in case there is a product with results and yet no specification for it.</p>
  <pre><code>SELECT Table_C.Spec from Table_A 
  LEFT JOIN (SELECT Product, Date, Spec from Table_B ORDER BY Date DESC) AS Table_C
  ON Table_C.Product=Table_A.Product
  WHERE Table_C.Date=(
  SELECT max(Table_B.Date) FROM Table_B WHERE Table_B.Date&lt;Table_A.Date and Table_A.Product=Table_B.Product) 
  ORDER BY Table_A.ID
  </code></pre>
  <p>I hope the example is easier to understand this time.</p>
  <p>Thank you,</p>
  <p>Diogo</p>